Re: History tracking in databases

From: Tim <tserverius_at_hologic.com>
Date: 1997/08/28
Message-ID: <3405C2A0.4F5D@hologic.com>#1/1

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

This can't be answered easily. There are different techniques for doing what you need. I think I may be able to give you some direction.

What you could do is in your box table have revision fields. For example

Table Name:

        tblBox
Fields:

	BoxId	(You can set this field to type counter if you want)
	BoxName (Opt.  You may have a description field also)
	Rev	(Revision.  This could be an integer field which is recommended.)


Optional Fields:
	EffDate	(This is the date this instance of this Box is effective)
	ObsDate	(This is the date this instance of this Box is obselete)
	Active	(Boolean(Yes/No) to indicate quickly that this is the active

box)         

The dates are optional because you could just pick the latest Rev of each box programmatically when display, reporting, etc. I recommend at least using the Active field but you must set it when a Revision becomes active.

There is a great deal more to it than this, but I hope I have helped.

Good Luck
-Tim S. Received on Thu Aug 28 1997 - 00:00:00 CDT