After reading a lot of SO questions about Keeping page changes history or How to version control a record in a database (for example), I can't find a real elegant solution to do the work.
Now, let's try to explain as clear as possible what we need, for this simple revision system, that allows registered users to post some articles, and other users to submit a revision of those articles, and then some moderators users to check those revisions.
MySQL database
The database contains an articles table with the following simplified fields:
ARTICLE(id, id_user, title, content, date);
To implement the revision/history versions, I guess that we'll have the following table:
REVISION(id, id_article, revision_id_user, id_moderator, revision_date,
revision_title, revision_content, revision_description, revision_check);
With the relation : ARTICLE 0,n <---> 1,1 REVISION
Workflow
A user create an ARTICLE, which is inserted in the ARTICLE table (terrific !)
Another user makes an update of this ARTICLE, this update is recorded in the REVISION table, and is queued for the moderator users. (revision_check=0).
A moderator user validate the REVISION(revision_check=1), then the ARTICLE(content) gets the REVISION(revision_content) value.
My questions
REVISIONs for an ARTICLE:REVISION or the original ARTICLE?REVISION could be submitted while the last isn't checked.Is there a way to record light versioning? By the way, is it possible to insert in the REVISION table, only the updated content through a SQL, PHP or js compare function? And how to display it like SO do it? Because I'm afraid that the REVISION table will be very heavy.
Bonus : how does SO?
Any idea, link, source, plugin (MySQL, PHP 5 and JS/jQuery) would be greatly appreciated.
I don't see a single answer with a plugin for your question, because of your personalized workflow.
About the revision workflow
It is your own vision of it, it doesn't seems too bad for your use. But I'm sure that some use cases should have to evolve by the way.
First point that I can see, you must lock the revisions until a revision is in progress AND until it is validated by a moderator. When it is in progress add ARTICLE(revision=progress) for example, to lock it, and avoid users to edit an article at the same time by displaying a message.
Second point, be careful, I believe that the author of the article could update it without any moderation process. For this reason, you'll have to set the ARTICLE(revision=progress) too, while the author updates his own article.
About recording a light version of the revisions in db
You could make a crazy function in php (or other), that creates an array, for each changes, like following :
array('1'=>array('char_pos'=>'250','type'=>'delete','length'=>'25','change'=>''),
'2'=>array('char_pos'=>'450','type'=>'insert','length'=>'16','change'=>'some text change'),
...);
As you can see, creating, formating and recording this in database could be very awerful and difficult to manage.
I think that there's no way to do versioning with MySQL. You could do something for versioning with an ORM like PROPEL but I don't think that the result will be what you expect...
Here, the better way seems to record the entire updated article for each revision, even if it grows your database. With your workflow you wont read a lot the REVISION table, so MySQL won't have an heavy load for it.
About the comparison display
You could use the Diff-Match-Patch plugin to hilight the updates between two contents, "differences" demo here. I think that SO uses Beyond compare (or similar) to hilight changes between revisions.
To read more about SO technologies, you can have a look at this page.