I am working on developing a website, and part of it is to host “swap” ads of a particular type that people will then be able to search to make contact with others who can swap with them.
I have written a web form with a lot of checkboxes, in which advertisers can indicate various facts about their items. In all of these cases, none, one or more of the boxes can be checked to indicate that their item has these properties.
I am trying to decide how to structure the database with this in mind, and have come up with an idea for tables in which each checkbox is represented by a column, which may be either true (checkbox ticked) or false (checkbox not ticked). The advert ID will be the primary key.
Is this a good way of designing the database? Will it be practical to search for a set of conditions (e.g. search for: checkbox 2 ticked in table 6, checkboxes 3 or 4 or 5 ticked in table 7, checkboxes 1 and 3 ticked in table 8 etc. etc.)?
The advert id can’t be the primary key of the properties table, because primary keys should be unique - that is, you can’t have more than one property per advert if you structure your tables that way. If you have a variable number of properties that can be coupled with various adverts, it’s probably better to use 3 tables: adverts, properties and a cross-table:
Adverts table: Id, Name, Description
Properties table: Id, Name
AdvertsProperties: advert id, properties id
Then for every property that an advert has, you insert a row in the advertsproperties table that links them together. You can then search on the advertsproperties table to find adverts that have certain properties.
But if you have a fixed, limited number of properties, it’s much easier to just make one table
Adverts table
Id, Name, Description, Property1, Property2, Property3 ....
Where the Property1 … columns are smallints or what have you to represent checked/unchecked.
Here is an article about database/schema design that might be helpful. It’s a bit dense, though. The first edition of the oreilly Mysql&msql book also has a good introduction to database design. You can read it on the safari bookshelf (I think you can get a free trail account)
Thanks.
Putting it all in one table I had considered, but it seemed like a rather ugly way of dealing with the problem. This one table would have 67 attributes! On the other hand, it probably makes programing the search quite a bit easier.
I’m not sure I understand why the advert ID can’t be the primary key in each of my 13 tables. They would be unique in that they’d all refer to the same advert. In table 1, it would be
advert_id | email_address
1 | you@gmail.com
and in table 7 it would be
advert_id | property1 | property2 | property3 | property4 | property5
1 | FALSE | TRUE | FALSE | FALSE | TRUE
I think there is something I’m misreading in your description. Why are there so many tables? It looks like you’re mixing the advert info and the “advert selection”… Using descriptive table names might help. As it is, I don’t see any advantage to using the two tables that you’re describing. You’re just spreading the columns from a single “adverts” table over two tables - basically, if every row in some table always corresponds to exactly one row in another table, you’re better off using only one table (arbitrary restrictions / performance issues in the database aside).
In any case, you should first figure out what the different “objects” are that you want to store. Cross-tables aside, try to give an intuitive name to all tables - like “adverts”, “properties”, “people” etc. In my example with the 3 tables above, the big advantage is that you can add properties later without modifying your code or the database schema - just add a row to the propery table. If you expect to regularly add/remove properties from you app, using that schema is probably the way to go.
I meant to add “and vice versa” - if you have a 1 to 1 relationship between the objects in both tables.