php - Database design: implementing several types of the same entityGet the solution ↓↓↓
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.
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.
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.
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
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
Share solution ↓
Link To Answer People are also looking for solutions of the problem: mark bundle as not supporting multiuse
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.