I have three different kinds of products i.e., vehicle, gold and property in our application. Each product has almost 30 different kind of its own inventory details. I have designed my table structure as below:-
Header Table:-
PkId ------ + ProductFkId------ + AssetId ------ + RegNo
1 | 1 | A00001 | R0001
2 | 2 | A00002 | R0001
3 | 3 | A00003 | R0001
Vehicle Inventory Table:-
PkId ---------------+ HeaderFkId -------- + Make ------- + Model
1 | 1 | Toyota | Etios Cross Dieselvdsp
2 | 2 | Toyoto | Etios Dieselgd
3 | 3 | Toyota | Etios Dieseld-4d Gd
Gold Inventory Table:-
PkId ------- + HeaderFkId ----- + Carat18 ----- + Carat19 ----- + Carat20 ----- + Carat21 ----- + Carat22 ------ + Carat23 ----- + Carat24
1 | 1 | 10 | 0 | 100 | 45 | 35 | 4 | 7
2 | 2 | 1 | 100 | 0 | 50 | 40 | 6 | 0
3 | 3 | 30 | 40 | 10 | 10 | 56 | 0 | 10
Property Inventory Table:-
PkId ----- + HeaderFkId ----- + MarketPrice ----- + ReservePrice ----- + PropertyDescription
1 | 1 | 300000 | 200000 | Flat No. 202 & 202-A
2 | 2 | 500000 | 400000 | Flat No. 201 & 201-A
3 | 3 | 200000 | 100000 | Flat No. 200 & 200-A
All together in Single Table:-
PkId --- + ProductFkId--- + AssetId --- + RegNo --- + Make --- + Model --- + Carat18 --- + Carat19 --- + Carat20 --- + MarketPrice --- + ReservePrice --- + PropertyDescription
1 | 1 | A00001 | R0001 | Toyota | Etios | Null | Null | Null | Null | Null | Null
2 | 2 | A00002 | R0002 | Null | Null | 5 | 84 | 56 | Null | Null | Null
3 | 3 | A00003 | R0003 | Null | Null | Null | Null | Null | 46000 | 500000 | Flat
Instead of storing product data in separate tables, I can also store in one table. Tomorrow any new product is coming at that time I need to create new table or adding additional 10 or 20 columns based on product in one table. If I save all product details in one table at that time except particular product other product columns will store null values. Which table design structure is best suitable for normalization?
Best Answer
It can sometimes be tempting to go down the EAV route (or even diabolically tempting), but hic sunt leones! This delightful image encapsulates the difficulties with such an approach - just like in primary school, you can't mix apples and oranges! Take a small amount of time to peruse the links to see where EAV can lead.
If I were you, I would do something like the following.
This schema should work on any server (tested on PostgreSQL), but obviously, you'll have to include Microsoft SQL Server specifics for auto-incrementing keys and syntax for the PK and FK constraints.
Other asset types can be readily added as required!