title: Adding a version/history system to database table
body: <p>I am working on a project where I have to add a form of data versioning or history feature to every table. Basically we have to keep track of every insert or change in the database, so that it is easy to roll back to or view previous versions of the data in each table.</p>
  
  <p>The way my project manager envisions this is to be done is to add a few new coloumns to each table. The main feature is a coloumn named "version". Every time an update is made, nothing is really updated, the old row remains but a new row is added to the table with an incremented value for "version". </p>
  
  <p>To show the current data, we just use a view that shows only rows with the highest version number of each type.</p>
  
  <p>While this works great as far as moving back and forth between different versions, I have run across a problem with this approach. For any relationship to exist between tables, we need to define foreign keys and foreign keys can only reference unique fields in the other table. Now that we are keeping several versions of the same row (with the same 'Id' as it is basically the same piece of data as far as our application is concerned) we can no longer use another table's 'Id' as a foreign key for a table. </p>
  
  <p>We are using a unique primary key field for each row, but that is useless as an identifier since several rows are basically different versions of the same thing. We could manually keep track of the latest version of each kind of entry and update corresponding foreign key relationships every time something is changed, but that looks like a a lot of work and I am not sure that will always even work (e.g. reverting to previous version of an entry might cause the foreign key to reference an old and unusable version of another entry in some other table.)</p>
  
  <p>I know there are other ways of keeping a history of database updates (e.g. by using a separate history table for each table), but I am stuck with this approach in this project.
  Is there some more obvious way of handling relationships between tables like this that I am missing?</p>
  
  <p>Note: I am using MS SQL Server 2008 R2.</p>