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

Create a new table called (say) BoxContents. In your BOX table, only include fields for what remains fixed ie BoxID, LoadedDate, Description.  In the new table, you would have fields for BoxID, Date, Contents (and anything else which changes from one instance to the next).

Selecting on BoxID and sorting on LoadedDate will give you all 'archived' instances.

HTH

Peter Barker                      pbarker_at_pcug.org.au