Need to do Versioning of existing system

I am working on a system which now requires versions to be maintained. Will explain you the project in brief. We have a CMS in which there are three users 1. Super Admin, 2. Editor, 3. Approver. Editor add's data from CMS and send it to approver if he approves the data only in that case it is visible on the front end. Now if the editor edits the same data then its disapproved and removed from the frontend and agian he will send it to the approver and the same process will follow. This is how its working now.

The client has now changed the requirement and now what he wants is that once the data is approved and visible in the site i.e frontend and the editor now edits the data it should not be removed from the frontend and new edited data should be send for approval and when the approver approves the data the old one should be replaced with new one in the front end.

The database design is real bad so it's going to be difficult to achieve this.

The solution that we have thought of is... For example the Editor can add some kind of news in the front end. All the data w.r.t news is added in news table. Editor add's the news sends it for approval and then he approves. Till here there are no issues. Now suppose that for some reason he wants to edit the same news which is approved. The moment he clicks on edits button it is removed from front end so what we thought is like maintain another table something like temp_news with the same structure and the moment he clicks on edit news after its being approved a new record should be inserted in temp_news table with the values from news table added in this table. Till then in frontend we will display the news from news table. So now we have the same news data in two tables. Approver and editor can 开发者_如何学Gonow do all the changes and we will update the temp_news table. The moment approver approves this changed data we will update the new's table with values from temp_news table. so now in front end we have the updated new's. The problem with this solution is that once the changed is approved both tables news and temp_news have the latest data. This not versioning. Need a solution for this.

I am just a beginner.

I would just have one table that you use to store multiple versions of the same data

news
----
ID           int
newsItem     int
dateCreated  datetime
IsApproved   boolean
content      text

Then for display on the front end. Just

SELECT top(1) * FROM news where IsApproved = true orderby dateCreated desc  

So the front will only show the most recently approved version.

and newer version will just get a new ID and dateCreated but the isApproved would be false so say if you had 3 version of the same news item. it would look like

ID    newsitem    dateCreated    IsApproved  
1         1        2011-4-26        false
2         1        2011-3-20        true
3         1        2011-1-6         true

How about a version control system like Git? Keep the versions, including the one to be approved in Git and the approved one in database table ( only one .) When a new one is approved, move it to the db. Since you are using C# and ASP.NET, something like git# might be helpful for you

People are already doing this:

This is a django website with a twist: all content is stored in text files. I manage those files with git. A little script loads those files into the database and then django serves them.

In fact, all of the content and its history is actually publicly available (but you don't have to make your history available in order to use this system). You can all see this an example of how to use django-gitcms.

http://luispedro.org/software/git-cms

http://nestacms.com/docs/creating-content/publishing-articles-with-git

Git-based content management?