Im trying to puzzle out the easiest method to keep history/revision of tables inside a relational database.

I've done some investigation and reading through, and am unsure an amount be the easiest method to start monitoring changes. For my primary tables, Im quite i have settled for any revision table, to help keep track(see picture) but it's the relation tables that I am not sure about. Maybe just an audit trail table holding the alterations?

Searching in the sample picture below, an amount be the easiest method to keep good reputation for the "movies_has_stars" table? I cant function just like the 2 primary tables(movies,stars), as I have to know which MOVIE revision_id which was the active one at the purpose of time the relation is made. And let's say I wish to toss in monitoring which USER added the relation (USER table not in sample picture) ? I shouldn't keep my way through the relation table itself, becuase which will just grow huge and decelerate queries...![alt text][1]

To summarize, what's the easiest method to keep history, of the relation table?

regards, modano

Connect to image:

[1]: Image

in SQL server 2008 a brand new feature known as CDC (Change data Capture) CDC on MSDN might help. CDC is definitely an capability to record changes to table data into another table without writing triggers as well as other mechanism, Change data capture records the alterations like place, update, and remove to some table in SQL server thus making the particulars from the changes obtainable in relational format.

Channel9 video -

This Stackoverflow posting talks about this problem.

I believe Audit tables make the perfect solution. In case your using SQL Server 2008 take a look at Change Data Capture. It is really an automatic change monitoring feature.

sorry, attempting to make the look visible... allow me to attempt to publish it inside a comment rather: