Which kind of table design best suitable for normalization in SQL server?

Question

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?

Answer 1

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.

CREATE TABLE vehicle
(
  vehicle_id INTEGER PRIMARY KEY,
  vechicle_make VARCHAR (50), -- another table?
  vehicle_name VARCHAR (50),
  vehicle_estimated_value INTEGER,
  vehicle_reserve INTEGER
);

CREATE TABLE gold
(
  lot_id INTEGER PRIMARY KEY, -- database generated
  dealer_id INTEGER, -- another table?
  lot_name VARCHAR (30), -- unique/ or unique with dealer
  gold_carat SMALLINT,
  gold_value INTEGER,
  gold_reserve INTEGER
);

CREATE TABLE portfolio
(
  portfolio_id INTEGER, -- database generated  
  pf_asset_type INTEGER, -- FOREIGN KEY from asset_type table
  pf_asset_id,           -- FOREIGN KEY from asset (gold) table

  -- make the combo of pf_asset_type and _id UNIQUE

  pf_asset_value INTEGER,
  pf_asset_reserve INTEGER      

  --  If I were you, I'd be using a TRIGGER to feed values 
  --  from the different asset tables into these two monetary 
  --  fields. Not sure of the exact capabilities of SQL Server,
  --  maybe some sort of non-updateable calculated field? 
  --  The advantage this has is that you're not having a massive 
  --  sum query for all your assets... Test and see what works for you.
);

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!