Re: Way to design "history" in DBs

In article <38BC0287.F33CAAE_at_iam.unibe.ch>,   Bernhard Nemec <nemec_at_iam.unibe.ch> wrote:
> I am creating a new relational database and don't know the best way to
> design the following special requirement:
>
> I have a table of entities (items) that are received, stored in
 various
> places, and treated in several possible ways. The user's requirement
 is
> now that not only the actual storage position and state of each item
 can
> be queried, but also its "history". That is, he wants to query when
 the
> item was received, when it was dislocated, and when it was treated in
> which way by which employee.
> You could think of a logfile that every item carries along, where all
> relevant happenings are written to.
>
> Are there common ways how to design such a thing in an E-R model?
>
> Is it the best idea to just add a "history" entity where the events
 are
> recorded as plain text?
> This would mean a big set of entries into the "history" table for
 every
> single item in the database.
>
> Thanks for any thoughts and ideas
> Bernhard
>
>

Bernhard,

First of all, let me say that I've tried working with two solutions to the problem you face. The first was to have a seperate archive table (the "free text" entity you suggest); the second was to adopt the technique used by the existing respondees and have start dates and end dates on everything that can change over time.

Both methods have their merits and limitations.

I tend to favour the second option, but there is an inherant danger to your data model if you go that way. The danger is that the "start date" may become part of the primary identifier for the entity it is being added to. This is not a big deal for that entity, but if there are any entities with a foreign key to that entity, they may also need to change.

I used to work on an insurance system where there were three levels of data which had date tracking added after the initial design and it was a nightmare to maintain.

The bottom line is that if your users want to know when (for example) a status was set, I'd favour the seperate history entity, but if you want to see the full picture at a particular date I'd go for the start and end dates and accept the probable structural changes required to support it.

Cheers,

     Tim

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 10 2000 - 00:00:00 CET