Re: How to model disjoint date ranges?

>> I have an attribute that changes with time in discrete steps e.g.
if something has a title that can change every now and then and we need to store the history of previous titles. <<

I prefer to do this with (start_date, end_date) pairs. The logic is that the duration is part of the fact that you are modeling. The pair is really like an (x,y) co-ordinate -- it has two parts in its representation, but it is a single atomic attribute.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMAREY KEY,
 ...
 start_date TIMESTAMP NOT NULL,
 end_date TIMESTAMP, --null means still on-going   ...);

When you do queries, the NULL in the end_date gives you what you need to always have the right facts:

To only the things still open:

 CREATE VIEW CurrentFoobar (..., start_date, end_date, ..)  AS SELECT ... start_date, CURRENT_TIMESTAMP, ...

      FROM Foobar
     WHERE end_date IS NULL;

To see the most recent examples, including those that are closed:

 CREATE VIEW MostRecentFoobar (..., start_date, end_date, ..)  AS SELECT ... start_date, COALESCE(end_date,CURRENT_TIMESTAMP), ...

      FROM Foobar
     WHERE end_date IS NULL
        OR end_date 
           = (SELECT MAX(endate)
                FROM Foobar AS F1
               WHERE F1.foo_key = Foobar.foo_key);

You get the idea. Received on Sun Jul 21 2002 - 16:22:54 CDT