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?