title: Denormalising a fully normalised table
body: <p>Can anyone advise on how to go about denormalising a fully normalised table? I've not been able to find much on it despite googling.</p>
  
  <p>Whenever a record is updated (though not inserted, but lets not worry about that now) in TableA a record is inserted into HistoryOfTableA with the values of the fields which have changed and an associated timestamp when it was carried out.</p>
  
  <p>eg.</p>
  
  <p>TableA fields:</p>
  
  <pre><code>TableA_Id, FieldA, FieldB, FieldC, FieldD.... etc
  </code></pre>
  
  <p>HistoryOfTableA records:</p>
  
  <pre><code>HistID, TableA_Id, FieldChanged, OldValue, NewValue, DateCreated
  1, 1, 'FieldA', 1, 2, &lt;2013-03-18 12:20:00&gt;
  2, 1, 'FieldB', A, B, &lt;2013-03-18 12:20:00&gt;
  3, 1, 'FieldC', A, B, &lt;2013-03-18 12:20:00&gt;
  </code></pre>
  
  <p>The situation is I'm looking to create some SQL for reporting purposes. So I want to be able to specify a point in time, and be able pull together those hostory table entries and work out what the state of that record from TableA was at that time.</p>
  
  <p>I'm thinking I can create tables based on the history table and joined on the date created and the Id from table A</p>
  
  <p>eg.</p>
  
  <pre><code>select HistA.NewValue, 
         HistB.NewValue, 
         .... 
  from FieldA_HistoryOfTableA HistA 
       inner join FieldB_HistoryOfTableA HistB on HistA.DateCreated = HistB.DateCreated
         and HistA.TableA_Id = HistB.TableA_Id 
       inner join ... etc
  where HistA.FieldChanged = 'FieldA'
  and HistB.FieldChanged = 'FieldB'
  and .... etc...
  </code></pre>
  
  <p>But I don't think this is going to give me all that I want and I may not be able to do this purely in SQL. As an aside there are 20 fields in TableA so trying to join 20 tables may not be wise.</p>