Skip to content
Advertisement

Database design: implementing several types of the same entity

I’m coding a Classified Ads web application. The application has several types of Ads:

  1. General ads(electronics, toys, pets, books…)
  2. Real estate (houses, apartments, terrains…)
  3. Vehicles (motocycles, cars, vans, trucks…)

Each type has several common fields (id, title, description) and also some that are exclusive to its kind:

  1. General Ads (no exclusive fields)
  2. Real estate (area, type-of-property…)
  3. Vehicles (type-of-vehicle, cubic-capacity, kilometers…)

What is the most recommended approach to this situation?

  • A table that contains all fields and leave empty the fields that
    don’t apply to the current recordset.
  • A main table with the fields common to all Ads, and an additional table for each type of Ad that has exclusive fields.
  • One table for each type of Ad.
  • Other

Advertisement

Answer

I would build a solution depending on various criteria :

  • If you believe the table will be large in the future (a lot of ads to be published), you may want to minimize the number of JOINs for better performance => option 1. “one table with empty fields when not relevant to ad type”

  • Previous comment applies especially if your data storage cost is low.

  • If you have to query the data against certain field values (e.g. house size, car kilometers), you might avoid the solution described by phpalix (ad_type | property | value) or Andy Gee since your SQL syntax will be a nightmare, and prefer to have all your data in the same table (again).

  • If there are A LOT of custom fields per ad type, you might prefer to separate each ad type in their own table, for easier maintenance and data storage optimization. Then you can either JOIN or UNION to query your ads lists.

I’ll add to my answer if i think of something else.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement