Re: Way to design "history" in DBs

At work we have a Workflow system on an AS400 that records a history: Each work object has a record in a worktable wich represents it's current status, queue, worktype, priority, etc. and every time anything in this table is changed the "After Image" is stored in the history table along with a timestamp for when the Workobject was 'locked' and a timestamp for when the workobject was updated, 'Unlocked'! and the userid of the worker. Amongst other things this info is used to closely monitor employees productivity!!!

Hope this helps

ps there is well over 100 Million records in the histroy table (and the system has only been running, incresingly, form 1996)

Graeme.

Tim Fitzpatrick wrote in message <8ab1dj$fsc$1_at_nnrp1.deja.com>...
>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 Wed Mar 15 2000 - 00:00:00 CET