Re: Way to design "history" in DBs

Hi!

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

You can simplify this a great deal, if you add a column IsEffective to MyTable. The only drawback is that you have to update the original row when inserting the new effective row. But as you said, it is a balance between efficiency and simplicity ;-)

Heinz Received on Thu Mar 02 2000 - 00:00:00 CET