CODEWITHSUNDEEP
databaserdbms
modano
modano

Reputation:

Table revision/history?

I'm 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, I'm quite sure I have settled for a revision table, to keep track (see picture), but it is the relation tables that I'm 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 can't 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 don't want to keep everything in the relation table itself, because 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?

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

[1]: Image

Upvotes: 2

Views: 1728

Answers (2)

ashishjaiman
ashishjaiman

Reputation:

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 - https://channel9.msdn.com/posts/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008/

Upvotes: 3

JoshBerke
JoshBerke

Reputation: 67128

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.

Upvotes: 0

Related Questions