Re: Way to design "history" in DBs

Hi Kyle,

Kyle Lahnakoski wrote:

> 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?
>
> The answer is a balance between efficiency and simplicity, as usual.
>
> What I do to may tables is add a "Date Effective" column, plus whatever
> other data is necessary to track the change. That date column, along
> with whatever set of columns was used as the primary key, is now the new
> primary key. The new tables can be reduced to the originals using
> views. That way the queries are simpler when only current data is
> important.
>
> TABLE DEFINITION:
>
> MyTable
> Key
> MyField1
> DateEffective
> ChangeInfo
>
> QUERY THAT RESULTS IN A CURRENT VIEW OF TABLE
>
> SELECT
> *
> FROM
> MyTable A,
> (
> SELECT
> Key
> Max(DateEffective)
> FROM
> MyTable
> GROUPBY
> Key
> ) B
> WHERE
> A.Key=B.Key AND
> A.DateEffective=B.DateEffective
> ;
>
> But this method can be wasteful if the tables have many attributes, and
> the
>
> ----------------------------------------------------------------------
> Kyle Lahnakoski Arcavia Software Ltd.
> (416) 892-7784 http://www.arcavia.com

Your solution looks remarkably like the model I have been working with a couple of years ago. I had already considered writing you about it, but I thought it might be to -heavy- for your requirements. Tough to say though, from a distance.

One remark going back to this experience: The example query you give is a relatively simple one. Things get a bit more complicated when you 'd like to know the state of things at a given moment in time. I think in order to have this option you should also add something like DateIneffective. You could then query using 'WHERE DateEffective < :date AND :date < DateIneffective'. By the way: the real fun starts when you should also allow for corrections being made, like someone ordered some item but sent it back within a week, things like that.

Have fun,

Ruud de Koter Received on Thu Mar 02 2000 - 00:00:00 CET