Re: History, Update, Simulation

Jochen,
In our shop we have to work with load of history (both past and future). We have been thinking, reading and trying a lot and came up with the following (which works fine for us anyway).

Suppose you have the classic database; departments and employees. Those are two entities, but with a history track they will occupy (at least) two tables;
Dep (the data which you don't want to track in history) Dep_Hist (the data which you do want to track in history) Emp (the data which you don't want to track in history) Emp_Hist (the data which you do want to track in history)

Dep:
- DepId (pk) -- Department Identification

• DepName -- Department Name ... Dep_Hist:
• DepId (pk)(fk) -- Department Identification
• SitStartDate (pk) -- The date on which this situation becomes/became true
• SitRegTime (pk) -- Timestamp when the data was stored
• SitEndDate -- The date on which this situation is no longer valid ... Emp:
• EmpId (pk) -- Employee Identification
• DepId (fk) -- Department Identification
• DepName -- Employee Name ... Emp_Hist:
• EmpId (pk)(fk) -- Employee Identification
• SitStartDate (pk) -- The date on which this situation becomes/became true
• SitRegTime (pk) -- Timestamp when the data was stored
• SitEndDate -- The date on which this situation is no longer valid ...

The SitEndDate has a default value of 31/12/9999. Making the SitRegTime also a primary key enables you to store more than one situation on a single day.

Selecting a specific situation can be done by SELECT * FROM Emp_Hist WHERE EmpId = 123 AND SitStartDate <= [today] AND SitEndDate > [today]

There are other rules to apply if you decide to work like this. If you want to I can write them down.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Sep 07 2000 - 17:07:59 CEST