Table revision/history?

Question

Hello,

Im trying to figure out the best way to keep history/revision of tables in a relational database.

I have done some research and reading, and am not sure what would be the best way to go about keeping track of changes. For my main tables, Im quite sure I have settled for a revision table, to keep track(see picture) but it is the relation tables that Im not sure about. Maybe just an audit trail table holding the changes?

Looking at the sample picture below, what would be the best way to keep history of the "movies_has_actors" table? I cant simply do as with the two main tables(movies,actors), as I need to know which MOVIE revision_id that was the active one at the point of time the relation was made. And what if I want to throw in tracking of which USER added the relation (USER table not in sample picture) ? I dont want to keep everything in the relation table itself, becuase that will just grow huge and slow down queries...![alt text][1]

So to sum up, what is the best way to keep history, of a relation table?

regards, modano

Link to image: img115.imageshack.us/my.php?image=44623598nv1.jpg

[1]: Image

Answer 1

in SQL server 2008 a new feature called CDC (Change data Capture) CDC on MSDN can help. CDC is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.

Channel9 video - http://channel9.msdn.com/posts/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008/

Answer 2

I think Audit tables are a good solution. If your using SQL Server 2008 check out Change Data Capture. This is an automatic change tracking feature.

Answer 3

sorry, trying to make the image visible... let me try to post it in a comment instead:

Answer 4

This Stackoverflow posting discusses this issue.