Re: [Q] Most logical way to keep history ?
Date: 2000/07/29
Message-ID: <8lu6v5$1oup$1_at_news.cybercity.dk>#1/1
Hi,
What happens on the project I am working on is that a history table exists for all major tables.
A rule ("trigger") exist for each table such that a procedure is activated
for each deleted or updated row. (These rules, procedures and history tables
are
autogenerated as they all follow a strict template).
The primary table contains "extra" columns for staff code and date/time of action (inserted/updated).
The history table contains all columns from the primary table plus a from date column and action column to register the type of action that placed row in history table (Updated or Deleted).
Every time a row is insert into the history table the valid from date is taken from the original date/time, and the current system date/time is used to update the updated/inserted row in the primary table, and at the same time set the "to date" of the history table.
From this it is now totally possible to see which member of staff made changes, plus a complete history of row, plus which period each row in the history table was the valid one, and also which action placed it in the history table.
Now for a complete history, including current validity, a view exists which unions the tables together, giving complete overview of data. From the type code it is possible to see if the row is one that is current, deleted or updated from the original.
Now be careful here, as depending on your database server, optimisation problems may exist with union views, especially when used in more complicated SQL.
(Actually our situation is much more complicated than this as each row in the primary table also has a from and to date of validity, so that, for example, the address table contains multiple rows for each person for each period that their address is valid. So that the operator can enter an address for one month in the future, at which point the system automically starts using the new address, ignoring previous addresses stored in the system.)
Andrew Received on Sat Jul 29 2000 - 00:00:00 CEST