Re: History, Update, Simulation

From: Jan Lenders <J.Lenders_at_Betuwe.net>
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

Original text of this message