sql - Database model with historical data -

firstly, going explain problem using example real life. let’s company , selling different means of transport, e.g. cars, buses, trucks, trains, planes, etc. let's have around 10.000.000 different items daily changes.

for each item have unique name (e.g. car audi a8 x or plane boing 747-200b y) x , y unique values. don’t worry naming because works fine.

for each item have special data. data depends on type, e.g. car: dimensions (length, width, height …), powertrain, etc. planes have e.g. length, interior width, wingspan, wing area, wing sweep, etc.

and problem … put data different excel files , paper database.

question 1: database model better?

idea #1: going create 1 table, called items going store name of product selling (e.g. car audi a8 x, plane boing 747-200b y, etc.). , in other tables (car, plane, train …) store data cars / planes / trains. if data of e.g. car have check table car. if data of e.g. train have check table train.

idea #2: should create 1 table going store item’s names (just in idea #1, items). , additional pivot table (e.g. data fields: item, key, value) able find informations?

question 2: need history of data. in first case have duplicate row e.g. table car because 1 fields different. idea #2 ... rows in pivot table data necessary have information if data valid (or when valid).

can please me? have no idea model better or using in production. ... there book storing historical data database?

thanks!

you present 2 problems us. first organizing specialized data subtypes (cars, buses, trucks, etc.). second dealing temporal (historical) data.

your idea #1 resembles design pattern known "class table inheritance". if search on phrase, find many articles outlining how works. these pretty confirm initial reaction, add lots more helpful detail. find numerous references previous q&a entries in site, , in dba site.

for alternate design, "single table inheritance". stores in single fat table, nulls in spaces don't pertain case @ hand.

i not sure mean storing in pivot table. i'm familiar pivot tables in excel, have used them results calculated ordinary tables data stored.

how deal historical data separate issue.