Best way to store revision information for quick access to view or revert changes

Question

What I am looking for is the most optimal solution for storing content revisions in an MSSQL database. We have an application that allows users to make changes to small HTML content blocks similar to a wiki, but we need to have tight audit control over the changes. At any point in time, the manager might want to look at previously submitted contents or revert the entire HTML block back to the previous state.

I had it setup in the past where we had a primary table that stored the HTML information (along with various other meta tags) and then an audit table that kept a copy of the entire data row everytime a change was made. What I am wondering is if this is the best way to go, or should I just keep 1 table with all the current records and the changes in it and just have a flag that lets me know which one is the current one?

Answer 1

I have had decent success with doing a bit of both.

One table that has the current version of the record and one table with every version (and a timestamp and an edited by) in another table.

This gives you the advantage of having a complete copy of each version without the overhead of having to sort out the current version from all the others at runtime. it also allows you to set much stricter permissions on the audit table and optionally implement the audit copy with triggers and completely hide the audit information from the rest of the app if you like that sort of thing.

Answer 2

One table with flags would be preferable IMHO.

The alternative adds in overhead (adding old record to archive table), with little in the way of benefit. Such is the efficiency of most SQL engines, that there is usually negligible overhead in finding the current version of a record compared to searching for the only record in the table.

In certain, high-volume applications, there might possibly be a case for the alternative, but not for a wiki.