Re: Way to design "history" in DBs

In article <38BC0287.F33CAAE_at_iam.unibe.ch>, Bernhard Nemec <nemec_at_iam.unibe.ch> writes
>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.

What you are describing are transactions. Each transaction can be described as an employee performing an action on a subject.

>You could think of a logfile that every item carries along, where all
>relevant happenings are written to.

Databases often log every transaction, you need a way to examine the contents of each log record.

>
>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.

Yes. That seems to be inherent in the user's requirement. You could minimise the storage requirement by making a table of objects and a table of actions and only storing the ID's of the employee, action and object.

It might also be possible to discard some types of transaction record, possibly after a suitable delay. You do need to consider how long these history records are to be kept, and how many there will be.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk