Re: History tracking in databases

In article <872763022.5320_at_dejanews.com>,   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?

I've implemented this kind of thing by creating a new table, say BOX_OLD, and using database triggers to automatically update the history table whenever a row changes. Included in both the BOX and BOX_OLD is a datetime stamp, so that when box information changes the BOX row gets a new datetime stamp (set to current) and a BOX_SAGA row is inserted with the old contents of the BOX row including the old datetime stamp.

The best solution to this problem really depends on the exact nature of the situation. There are complications like deleting boxes etc.

Best wishes,

John H. Frantz           Power-4gl: Extending Informix-4gl
frantz@centrum.is        http://www.rl.is/~john/pow4gl.html

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet