I've often thought about this one too. I've seen implentations that used most of the tricks described in Greenspun's article, and also some that treated a database like CVS. Allaire Spectra serialized all data objects into XML blobs when storing them in the database, which made versioning easier but was not very efficient. Interwoven TeamSite collects all of your data in XML and then publishes it to the database using an XML-to-RDBMS bridge, but that means duplicating your data structure definitions and is basically a kludge. Most people end up just giving up on the idea of real versioning and using some kind of assembly stage instead, i.e. assemble everything on an internal content server and then periodically publish it all to the live server, like making a release from CVS.

For most content management problems like this, I think the multi-part key (id + version #) is the best way to go. You just keep a VERSIONS table which stores the current version number for each versioned table. You can even make this work for relationship tables if you choose a side that the relationship is from, e.g. you link foo (version 19) to bar (latest). If you put version numbers on both sides of the link and then one of the objects gets reverted, you can't easilly resolve it.

In order to make this easier to deal with from a progrmming perspective, I would suggest wrapping a database view around it. Then you can have simple keys in your queries and the view will join against the VERSIONS table and show the right one.

In reply to Re: Version Control in Database Applications by perrin
in thread Version Control in Database Applications by samtregar

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.