In my opinion, the second approach of having validity dates ("valid from", "valid to") is the better approach, because it allows you to easily time-limit changes. The queries for what is valid "now" are easily hidden in a view.

As an alternative approach, consider only keeping the current version in one table and the journal of changes in a secondary table. Reconstructing the old situation then consists of reading the data from the current table and reverting all changes to it from the journal, which is hard to get right. But if your concern is mostly auditing and review/selected rollback of the changes, a journal is much easier to handle in my experience.

You can set up such a journal either with database triggers (foolproof) or by copying the data in your program (less clutter in the journal, also allows for ephemeral information like "change reason", "change place in program" and "changing user", which the database doesn't necessarily know about.

In reply to Re: OT: Looking for good solutions to implement revision control with an EAV db model? by Corion
in thread OT: Looking for good solutions to implement revision control with an EAV db model? by isync

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.