CODEWITHSUNDEEP
phpmysqldatabase
Mick Murillo
Mick Murillo

Reputation: 45

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?

Upvotes: 3

Views: 376

Answers (5)

darma
darma

Reputation: 4747

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.

Upvotes: 2

Andy Gee
Andy Gee

Reputation: 3315

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

Upvotes: 1

Matthew
Matthew

Reputation: 9949

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.

Upvotes: 1

Sebas
Sebas

Reputation: 21522

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.

Upvotes: 1

phpalix
phpalix

Reputation: 689

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

Upvotes: 0

Related Questions