php - Database design: implementing several types of the same entity

Solution:

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.

Solution:

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.

Solution:

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
Copy code

Solution:

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