Home -> Usenet -> comp.databases.theory -> Re: How to model disjoint date ranges?

Re: How to model disjoint date ranges?

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 21 Jul 2002 14:22:54 -0700
Message-ID: <c0d87ec0.0207211322.17238ff2_at_posting.google.com> 






>> 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 - 23:22:54 CEST












Original text of this message