title: How to create a versioning/history/revision system for contents published by users?
body: <p>After reading a lot of SO questions about <a href="https://stackoverflow.com/questions/3481392/keeping-page-changes-history-a-bit-like-so-does-for-revisions">Keeping page changes history</a> or <a href="https://stackoverflow.com/questions/323065/how-to-version-control-a-record-in-a-database">How to version control a record in a database</a> (for example), I can't find a real elegant solution to do the work.</p>
  <p><em>Now, let's try  to explain as clear as possible what we need</em>, 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.</p>
  <p><strong>MySQL database</strong></p>
  <p>The database contains an articles table with the following simplified fields:</p>
  <pre><code>ARTICLE(id, id_user, title, content, date);
  </code></pre>
  <p>To implement the revision/history versions, I guess that we'll have the following table:</p>
  <pre><code>REVISION(id, id_article, revision_id_user, id_moderator, revision_date, 
           revision_title, revision_content, revision_description, revision_check);
  </code></pre>
  <p>With the relation : <code>ARTICLE 0,n &lt;---&gt; 1,1 REVISION</code></p>
  <p><strong>Workflow</strong></p>
  <ul>
  <li><p>A user create an <code>ARTICLE</code>, which is inserted in the <code>ARTICLE</code> table (<em>terrific !</em>)</p>
  </li>
  <li><p>Another user makes an update of this <code>ARTICLE</code>, this update is recorded in the <code>REVISION</code> table, and is queued for the moderator users. (<code>revision_check=0</code>).</p>
  </li>
  <li><p>A moderator user validate the <code>REVISION(revision_check=1)</code>, then the <code>ARTICLE(content)</code> gets the <code>REVISION(revision_content)</code> value.</p>
  </li>
  </ul>
  <p><strong>My questions</strong></p>
  <ul>
  <li>Is this workflow seems to be a good way to do it? Because, I see a mistake: if there are several <code>REVISION</code>s for an <code>ARTICLE</code>:</li>
  </ul>
  <ul>
  <li>Should we take the content of the last submitted <code>REVISION</code> or the original <code>ARTICLE</code>?</li>
  <li>Or, should we need to block the revisions as no other <code>REVISION</code> could be submitted while the last isn't checked.</li>
  </ul>
  <ul>
  <li><p>Is there a way to record light versioning? By the way, is it possible to insert in the <code>REVISION</code> 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 <code>REVISION</code> table will be very heavy.</p>
  </li>
  <li><p>Bonus : how does SO?</p>
  </li>
  </ul>
  <p><strong>Any idea, link, source, plugin</strong> (MySQL, PHP 5 and JS/jQuery) would be greatly appreciated.</p>