title: DataModel for Implementing row versioning in OLTP system
body: <p>I have following table </p>
  
  <ul>
  <li><p><em>Person(PID, Name, Dob, AddressId)</em> PID being surrogate key(auto incremented)</p></li>
  <li><p><em>Address(AddressId, Line1, Line2, City, State)</em> AddressId being surrogate key(auto incremented)</p></li>
  </ul>
  
  <p>PersonID (PID) is used in my lot of transaction tables. Since address of a person may change over the period of time, I need to store record version in database so that I can refer correct version according to time slice. How should I handle it.</p>
  
  <p>I am thinking of having solution something like this</p>
  
  <ul>
  <li>Using same table to store history
  e.g.</li>
  <li>Person(PID, Name, Dob, AddressId)</li>
  <li>Address(AddressId, Line1, Line2, City, State, StartDate, EndDate,
  LastUpdatedBy, LastUpdatedDate)</li>
  </ul>
  
  <p>Let's assume <strong>initial</strong> record of person as below</p>
  
  <pre><code>Person(100, X, Y, 110)
  Address(110, A, B, C, D, 2011-01-01, 9999-12-31, Y, 2011-01-01)
  </code></pre>
  
  <p>Now address was changed in <strong>2012</strong> for PID 100. I am thinking to do</p>
  
  <pre><code>Address(320, A, B, C, D, 2011-01-01, 2012-06-01, Y, 2011-01-01) - INSERT
  Address(110, A, B, Q, D, 2012-06-01, 9999-12-31, Z, 2012-06-01) - UPDATE
  </code></pre>
  
  <p>if address changes again in <strong>2014</strong> for PID 100. It would look like</p>
  
  <pre><code>Address(740, A, B, Q, D, 2012-06-01, 2014-06-01, Y, 2012-01-01) - INSERT
  Address(320, A, B, C, D, 2011-01-01, 2012-06-01, Y, 2011-01-01) - NO CHANGE
  Address(110, A, B, N, D, 2014-06-01, 9999-12-31, R, 2012-06-01) - UPDATE
  </code></pre>
  
  <p>Now I can query historical data as well as current data from same table without changing surrogate key. I won't have to refer historical tables hence no bombarding of logic and tables.</p>
  
  <p><strong>This approach won't let me enforce unique key constraint on tables assuming I have Address Code being unique. How shall I handle this?</strong></p>
  
  <p>Please suggest different approach and feedback on this.</p>