3

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
philipxy
  • 14,867
  • 6
  • 39
  • 83
Mick Murillo
  • 45
  • 5
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Nov 19 '22 at 21:24

5 Answers5

2

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.

darma
  • 4,687
  • 1
  • 24
  • 25
  • Thank you darma. I'll go for the one table solution, it is the easiest to implement and (as you say) it provides better performance: If the site is successful thousands of ads are expected. – Mick Murillo Feb 05 '13 at 13:58
  • While still trying to avoid a flame-war on the topic, this is my least preferred solution. Unless you are expecting a performance issue (billions of transactions?) this approach makes you code more difficult to test, extend, and maintain do to the unneeded complexity per object / entity type to deal with unused fields. There are many cases where this is the best solution, but if I was unsure I would not make it my default pick. – Matthew Feb 05 '13 at 14:24
1

You can normalise (a table for the abstract concept and a table the the specialised one) or denormalise (a table with all the fields)

As always, the choice must be done according to the cost of each solution, reprensented by the speed of the queries (normalised model means more joins (buffer/cpu) whereas denormalised more disk reads usually because the columns are sometimes retrieved when it is not necessary) or the storage required in both cases.

Sebas
  • 21,192
  • 9
  • 55
  • 109
1

All solutions are acceptable and a matter of preference, performance, complexity and design needs. The terms for what you are discussing are Table-Per-Type, Table-Per-Class and Table-Per-Hierarchy. If you google on these you are guaranteed to get a ton of Entity Framework results, but the underlying design considerations are much the same.

Matthew
  • 9,851
  • 4
  • 46
  • 77
1

For flexibility I would have all the field in a separate table then allow the assigning of each field to each ad type. This would also allow you to add and remove fields easily at a later date. Each field may have different types of data so this information should also be in a separate table.

Something like this (not very clear sorry)

Table: fields
field_id, field_type, field_name
1         1           title
2         1           price
3         2           size
4         3           description
5         1           square meters

Table: field_types
field_type_id, type
1,             textbox
2,             select_box
3,             text_area

Table: field_data
field_data_id, ad_id, field_id, field_type_id, field_data
1              1      1         1              Cool t-shirt
2              1      2         1              5.99
3              1      3         2              L,XL,XXL,XXXL
4              1      4         3              Some description
5              2      1         1              Nice house
6              2      2         1              250000
7              2      4         3              Some description
8              2      5         1              1024sq/m

Table: ad_types
ad_type_id, ad_type_name, fields
1           general       1,2,3,4
2           real_estate   1,2,4,5
Andy Gee
  • 3,149
  • 2
  • 29
  • 44
0

Well, store the values in columns and not in rows, so create a table and have 3 columns: ad_type, property, value

define your properties for each type of ad and query the ad type for its fields.

Hope that helps

phpalix
  • 679
  • 4
  • 8