Re: History tracking in databases

> bhq001_at_email.mot.com writes:

 ..
> As part of the design requirements, the database
> must provide means of
> tracking historical data. ...
> 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?

..

CRATE1 doesn't sound like part of the problem. But if boxes could be moved to various crates you should avoid making CRATE1's PK part of BOX1's PK; just hold it as an FK.

PeopleSoft deals with this situation with a single date field, EFFDT, in CRATE. If more than one change in a day is possible they further qualify it with EFFSEQ. This way, you only have to update one row to change the history. Each row is implicitly in effect until the next most current EFFDT in the table.

• Paul de Anguera, City of Seattle ESD/HRIS