I make datatables where I store advertisements.
My advertisement table looks like follows:
Advertisement:
id (PK)
title
description
category_id (FK)
..
I have 2 different types of advertisements: I store cars and I store real estate. Therefore I have attributes which differ for both types. i.e. cars have a brand and number of doors whereas real estate have number of rooms..
Therefore I would like to ask what is the best way to store this relation.
Should I put all values in one big advertisement table, but there is the disatvantage that some values are allways null.
Or should I make 2 additional tables and keep the common values in the advertisement table?
CarTable
id(PK)
advertisement_id (FK)
brand
numberOfDoors
...
RealEstateTable
id(PK)
advertisement_id (FK)
nrOfRooms
...
I don't think that the second solution with 2 additional tables is very good. Any suggestions? Thank you
One of the ways could be:
Advertisement:
id (PK)
title
description
category_id (FK)
..
Attribute type:
id (PK)
name
code (can be optional)
Advertisement's attributes:
id (PK)
Advertisement id (FK)
Attribute id (FK)
Attribute value
I think the second one is the right one. Of course it is dependent of how big is the database and how many are that null columns. I'm thinking so because:
There will be no any empty cells for a difference of types
Usually for many column tables we are using 'SELECT * ...' CLAUSE but it isn't good and in your case all these NULL fields will load the traffic
At all data base engines are optimized to make relations between tables - in case you have hard defined relation by key it isn't a problem.