How to design a Table in which its fields varies on its categories

database-designrelational-theorytable

I am trying to design a database to post free ads. Eg http://www.gumtree.com.au/p-post-ad.html

Scenario: Every ad will have different categories and some categories will have further sub categories. SO the table AD will have different fields depending on its category. For example, an ad for used car will have MAKE, MANUFACTURER etc but an ad for books will have Book title field, Author field etc.

SO how do I design the table for ads?

I can possibly think of two solutions. One, to create subtypes for categories in table ad. Two, to create individual table for each categories of ad (this does not sound like a good design).

Best Answer

enter image description here

Store the information in tabular format. It will also help you in validating at application level. Store your field type in property table. and values in columns table.

Hope it helps.