From: jcelko212@earthlink.net   
      
   To track the history of, say, Foobars we need to see time as a continuum and   
   model it as (begin_date, end_date) pairs that define when a foobar had a   
   particular value. Here is the skeleton.   
      
   CREATE TABLE Foobar_History   
   (foo_id CHAR(9) NOT NULL,   
    start_date DATE NOT NULL,   
    end_date DATE, --null means current   
    CHECK (start_date <= end_date),   
    foo_status INTEGER NOT NULL,   
    PRIMARY KEY (foo_id, start_date));   
      
   When the end_date is NULL, that state of being is still current. You use a   
   simple query for the status on any particular date;   
      
   SELECT *   
     FROM Foobar   
    WHERE @in_cal_date   
        BETWEEN start_date   
         AND COALESCE (end_date, CURRENT_TIMESTAMP);   
      
   There are more tricks in the DDL to prevent gaps, etc   
      
   CREATE TABLE Events   
   (event_id CHAR(10) NOT NULL,   
    previous_event_end_date DATE NOT NULL   
    CONSTRAINT Chained_Dates   
     REFERENCES Events (event_end_date),   
    event_start_date DATE NOT NULL,   
    event_end_date DATE UNIQUE, -- null means event in progress   
     PRIMARY KEY (event_id, event_start_date),   
    CONSTRAINT Event_Order_Valid   
     CHECK (event_start_date <= event_end_date),   
    CONSTRAINT Chained_Dates   
     CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)   
   -- CHECK (previous_event_end_date + INTERVAL '01' DAYS) = event_start_date)   
   );   
      
   -- disable the Chained_Dates constraint   
   ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;   
   GO   
      
   -- insert a starter row   
   INSERT INTO Events(event_id, previous_event_end_date, event_start_date,   
   event_end_date)   
   VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');   
   GO   
      
   -- enable the constraint in the table   
   ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;   
   GO   
      
   -- this works   
   INSERT INTO Events(event_id, previous_event_end_date, event_start_date,   
   event_end_date)   
   VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');   
      
   -- this fails   
   INSERT INTO Events(event_id, previous_event_end_date, event_start_date,   
   event_end_date)   
   VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15');   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)