title: Database model with historical data
body: <p>Firstly, I am going to explain my problem by using example from real life.
  Let’s say that we are company and we are selling different means of transport, e.g. cars, buses, trucks, trains, planes, etc.
  Let's say that we have around 10.000.000 different items with daily changes.</p>
  
  <p>For each item we have an unique name (e.g. car Audi A8 X or plane Boing 747-200B Y) where X and Y are unique values.
  Don’t worry about naming because it works just fine.</p>
  
  <p>For each item we also have some special data. Data depends on type, e.g. for car: dimensions (length, width, height …), powertrain, etc. For planes we have e.g. length, interior width, wingspan, wing area, wing sweep, etc.</p>
  
  <p>And now the problem … I would like to put all this data from different Excel files and paper to database.</p>
  
  <p>Question 1: Which database model is better?</p>
  
  <p>Idea #1: I am going to create one table, called <em>items</em> where I am going to store only name of product which we are selling (e.g. car Audi A8 X, plane Boing 747-200B Y, etc.). And than in other tables (<em>car</em>, <em>plane</em>, <em>train</em> …) I will store extra data for cars / planes / trains.
  So if I would like to get all data of e.g. car than I will have to check table <em>car</em>. If I would like to get all data of e.g. train than I will have to check table <em>train</em>.</p>
  
  <p>Idea #2: Should I create one table where I am going to store all item’s names (just like in Idea #1, <em>items</em>). And than additional pivot table (e.g. <em>data</em> with fields: item, key, value) where I will be able to find all informations?</p>
  
  <p>Question 2: I need history of all data. In first case I will have to duplicate row from e.g. table <em>car</em> just because one fields is different. But for Idea #2 ... for all rows in pivot table <em>data</em> would be necessary to have information if data is valid (or when was valid).</p>
  
  <p>Can you please help me? I have no idea which model is better or what is actually using in production. Also ... is there any good book about storing historical data to database? </p>
  
  <p>Thanks!</p>