Re: ERD for time-based relationships

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 10 Aug 2003 13:33:46 -0700
Message-ID: <a264e7ea.0308101233.6eeb04a7@posting.google.com>

I usually use a history table, something like this:

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL,

 start_date DATE NOT NULL,
 end_date DATE, -- null means current
 CHECK (start_date < end_date),
 ...
 PRIMARY KEY (foo_id, start_date));

and a calendar table with all the enterprise temporal data in it -- holidays, fiscal calendar, etc.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,

 ...);

And then write queries like this:

SELECT ...
  FROM Foobar AS F1, Calendar AS C1
 WHERE C1.cal_date BETWEEN F1.start_date

                   AND COALESCE (F1.end_date, CURRENT_TIMESTAMP)

   AND ...; The COALSECE() makes the data current and I can also pick ranges of calendar dates. Received on Sun Aug 10 2003 - 15:33:46 CDT