Re: History tracking in databases

bhq001_at_email.mot.com wrote in article <872763022.5320_at_dejanews.com>...

> 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?

I think you'll need 2 extra fields in (for your example) the BOX table, for date sensitivity. For example, if you add StartDate and EndDate fields, it could work like this:

BOX1 is added to the database - update the StartDate with the date the box was added.

BOX1 changes - add a new record for BOX1 with the StartDate set to the date the information changed. Set the EndDate on the previous BOX1 record to the date the information changed.

Get it?

Depending on how much information is actually in your BOX table, you may want to track the changes is a third table so you don't have to repeat ALL of the information each time a record changes - all depends on your specific application. Received on Thu Aug 28 1997 - 00:00:00 CDT