3

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?

5

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!

  • Here portfolio table will act as main table i.e super table, and vehicle and gold tables will act as sub tables. Is my understanding correct? What is the purpose of pf_asset_value and pf_asset_reserve columns maintaining in Portfolio table because vehicle_reserve and gold_reserve columns have been already maintaining in Vehicle and Gold Tables respectively? – RGS Sep 27 '17 at 17:40
  • 1
    Look on portfolio as a summary table (maybe a materialized view?). The fields pf_asset_value and pf_asset_reserve are never updated directly but instead come from the "subtables". Use a trigger, MV (or virtual) field - I'm not an expert on SQL Serverso I'm not sure of the best approach for that RDBMS. If you're not, perhaps a new question with a link back here (and a comment to let me know :-) ). – Vérace Sep 27 '17 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.