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

Question

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).

Answer 1

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.

Answer 2

I would avoid having completely separate tables for each ad type, that would be bad design (you'd end up having to UNION them all together in just about every query). Also avoid having all the fields possible in one table as that would be inefficient also, especially if your database doesn't support sparse rows.

The usual technique for this sort of problem is "table inheritance" (so search that term for more examples, whereby you have one core table that contains all the common properties (in this case things like date, caption, description and owner), a column to indicate the type of advert and an extra table for each type. So for a car the ad_type column would contain "car" and the details would be found ad_detail_car table. Something like:

ad_common          ad_detail_car
===========        =============
advert_id   -----> advert_id
advert_type   |    make
caption       |    manufacturer
description   |
owner         |    ad_detail_book
price         |    ==============
              |--> advert_id
              |    isbn
              |    binding
              |
              |    ad_detail_kitten
              |    ================
              `--> advert_id
                   breed
                   age

advert_id would be the PK for the common table and both the PK and an FK to the common table in the detail tables.

As the number of advert types grows having a separate table for each might become a mess so you might instead consider a property bag like so:

ad_common          ad_extra_properties
===========        ===================
advert_id   -----> advert_id
advert_type        property_name
caption            property_type
description        property_value
owner             
price             

Here the PK for the property bag is advert_id,property_name. Be warned though, this is a pattern (called the EAV or "Entity Attribute Value" pattern) that has its own set of problems, particularly for reporting, so read around that before considering if it is suitable for your needs.

Another option if going for the EAV model is to do away with the detail or property bag tables completely and just store the extra properties in a format useful for the business logic (JSON, XML) in a column of the main table. This is simpler but means (unless your chosen DB natively supports such documents) you can't report on or filter/sort by the extra properties at all directly in the database.

Answer 3

You will need several tables to accomplish your design. One for ads, one for categories and one for the ad properties (make, manufacturer, book title, isbn, etc...).

Here is a sample design:

• Table Advertisement. This will contain the ad instances.

  1. ID - primary key
  2. Title
  3. Category_ID - foreign key to Category.ID. Tells us the ad category.
  4. Description
  5. Other general fields

• Table Category. This will contain information and description about different ad categories.

  1. ID - primary key
  2. Name
  3. Description
  4. Parent_category - self-referencing foreign key to Category.ID. This enables multiple category levels.
  5. Other general fields
• Table Property. This will contain information about all defined types of properties and to which category they belong.
  1. ID - primary key
  2. Name
  3. Description
  4. Category_ID - foreign key to Category.ID. Tells us for which category this property is relevant.
  5. Other description fields
• Table Property_value. This will contain the actual values of the property.
  1. ID - primary key
  2. Property_ID - foreign key to Property.ID. Tells us what the property is.
  3. Advertisement_ID - foreign key to Advertisement.ID. Tells us for which ad this property is set.
  4. Value

It is quite a few tables and if you want to have pre-set values ( for choosing from drop-downs, etc. ) you will need to make some changes. If you need to have many-to-many or one-to-many relationships somewhere ( for example one ad belonging to multiple categories or one property relevant for multiple categories ) you will need additional tables to maintain the relationships.

Most probably there is a more efficient design, but this should get you started.