Re: History, Update, Simulation
Date: Thu, 07 Sep 2000 15:07:59 GMT
Message-ID: <8p8avs$o78$1_at_nnrp1.deja.com>
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