Database design: implementing multiple types of the same object
I am coding a web app for ads. The app has several types of ads:
- General advertising (electronics, toys, pets, books ...)
- Real estate (houses, apartments, plots ...)
- Vehicles (motorcycles, cars, vans, trucks ...)
Each type has several common fields (id, title, description), as well as some that are exclusive to its type:
- General declarations (no exclusive fields)
- Property (area, type of property ...)
- Vehicles (vehicle type, cubic capacity, kilometers ...)
What is the most recommended approach for this situation?
- A table that contains all the fields and leaves blank fields that do not apply to the current recordset.
- A main table with fields common to all ads, and an additional table for each type of ads with exclusive fields.
- One table for each ad type.
- Other
This is my first professional PHP / MySQL application, sorry if the question is too simple. I just want to be sure that the solution I am implementing is the best one. Please discuss why and how you can implement your recommended solution.
source to share
I would build a solution based on various criteria:
-
If you think the table will be big in the future (many announcements will be posted), you can minimize the number of JOINs for better performance => option 1. "one table with empty fields when this is not an ad type"
-
The previous comment applies especially if your storage cost is low.
-
If you need to query data against certain field values (e.g. house size, car kilometers), you can avoid the solution described by phpalix (ad_type | property | value) or Andy G., as your SQL syntax will be a nightmare and prefer to have all your data in one table (again).
-
If each ad type has many custom fields, you can choose a separate ad type in your spreadsheet to make it easier to optimize serving and data storage. Then you can use JOIN or UNION to query your declarations.
I'll add to my answer if I think of something else.
source to share
You can normalize (a table for an abstract concept and a specialized table) or denormalize (a table with all fields)
As always, the choice should be based on the cost of each decision, which affects the query speed (a normalized model means more joins (buffer / cpu), whereas denormalized reads more disks, usually because columns are sometimes fetched when not necessary) or storage required in both cases.
source to share
All solutions are acceptable and depend on preference, performance, complexity and design. The terms for what you are discussing are Table-Per-Type, Table-Per-Class, and Table-Per-Hierarchy. If you've pointed them to Google, you're guaranteed to get a ton of Entity Framework results, but the basic design considerations are the same.
source to share
For flexibility, I would get the entire field in a separate table and then allow each field to be assigned to each ad type. It will also allow you to easily add and remove fields at a later date. Each field can have different data types, so this information must also be in a separate table.
Something like this (not very clear)
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
source to share