What is the best way to implement versioning to a MYSQL database?

Question

Hi,

I have to produce a versioning system to store multiple versions of my articles in a database to allow the user to roll-back if required. My first thoughts are when the user wants to edit an article really I get the sql to no longer update but you insert their information back into the database. Currently the problem is i have an articles table and I am using the ID as my primary key to identify my article (so read more links and so on). If i insert into that table I will have basicly new articles, so I am thinking I need a new table for the older articles but then I have problem of the originals in that first table dbArticles, I also need so why of telling which article they have choosen, for example if they have 3 edits so 3 articles lets all made on seperate days may 1st,2nd and 3rd how I need to show they have choosen the 2nd of May version rather than the latest on the 3rd of May. Any help would be greatly receive thanks,

Cool Hand Luke Uk

Ps I am using PHP also and I am timestamping articles :D.

Answer 1

Have 2 tables:

• article: here you store article_id, article name and any other meta data
• article_version: Here you store each version of the article (including the original one) and link with article_version.article_id=article.article_id. Be sure to add a timestamp and version etc in this table.

Answer 2

If you are after versioning of textual articles, you should consider using a wiki, for example MediaWiki. They may have solved the problem for you.