If you do ever decide to try and implement a diff system (I have thought about this problem a lot myself), remember to think about how your system will handle two simultaneous updates to a piece of content. There could be a race condition in the system you've outlined where, as you are doing the SELECTing and diffing required to generate the new change entry, a new row is entered in changes, for example from an update begun just before yours, for the same id. Now your diff is totally wrong.

Also, I don't know about the latest versions of MySQL, but in my 3.x datestamp only is granular to the second level, and it is not inconceivable for a central piece of content on a busy site like a Wiki to get two changes entered within the same one-second window, so there would be no way to select which change to apply first because they both have the same timestamp. (Although this eventuality could be mitigated by designating PRIMARY KEY(id, timestamp) which should throw an error on simultaneous update)

In reply to Re: Vagueness and patched strings by ryantate
in thread Revision control in relational databases by Your Mother

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.