Re: Way to design "history" in DBs

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