I'm working on a modification to a site to allowing users to enter content. However, once they've created that content it needs to be approved before it can be published. This is fairly easy to achieve by having a "moderate" bit set in the record for that content and only one table is needed.
Now I also want to allow users to edit the content much like here on StackOverflow. However, each edit needs to be approved as well as being stored so that a history of edits can be viewed.
It is acceptable to prevent editing to the "live" record while an edit is pending approval so conflict of pending edits is not an issue.
My intention is to keep the collection of previous versions of each item in a separate table and have the pending edit sit in this table until approved/rejected and then if approved swapped with the record in the main table.
Can anyone see any flaws in this pattern? Can you think of a better way to do this?
Although I don't think that it's relevant I'm using C# with ASP.NET MVC and SQL Server as the data store.