Re: Way to design "history" in DBs

One "brute-force" method is to employ the use of UPDATE and DELETE triggers on the base table. The triggers would copy the BEFORE image into a "history" table, adding a few attributes like date-time of action, user-id of user who did it, type of action, etc. The primary key, if that's essential, on the history table would be that of the original table plus the date-time column.

This method has, at least, 3 notable advantages a. The original table remains "slim"
b. You are keeping an entire history, so irrespective of a change of requirements (they always change!), you've got everything. c. Its simple to implement.

Rudy

Bernhard Nemec 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
Received on Tue Mar 07 2000 - 00:00:00 CET