Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.