Re: History tracking in databases

bhq001_at_email.mot.com wrote:
>
> Hi,
>
> I am trying to develop a relational database for a
> project at work.
> Currently we are at the logical design phase for
> this database.
> The database structure is very hierarchical due to
> the data requirements
> of the project.
> As part of the design requirements, the database
> must provide means of
> tracking historical data. This is further explained
> below:
>
> Say you have a CRATE (CRATE1) which can contain
> smaller BOXes (BOX1,BOX2
> etc.)
>
> CRATE1 is related to BOX1 and BOX2.
>
> Each BOX contains information specific to itself.
>
> Say BOX1 information was changed for some reason but
> we would still like
> to know the previous information for BOX1.
>
> CRATE1 is now related to BOX1,BOX1(old),BOX2
>
> Question: How would this kind of relationship be
> implemented in an RDBMS?
>
> Thanks in advance for any advice given.
>
> H.Quan
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

It would seem to me that you would want to store the ID of the Crate in each of the Box records. To update the "contents" of Box2, still keeping the original data, you would create a new record for Box2, set its status to Active and update the contents. You would set the status of the "old" Box2 to Inactive, possibly with a Status_Changed_Date (if you wanted to know when the box was updated).

To get a listing a boxes for a given crate, you would find the crate ID, and then list all boxes that had the same crate ID value. You might sort them by Status and Status_Changed_Date to get the active and inactive boxes sorted out, or you might sort them by box number, so you could see that there were 3 versions of Box2....

HTH,

--
    Darryl Johnson                  Darryl.Johnson_at_nlc-bnc.ca
    National Library of Canada
      **  None of the above has any official status **