Re: ERD for time-based relationships
From: Alexey Kirich <kirich_a_at_softline.kiev.ua>
Date: Wed, 20 Aug 2003 11:30:43 +0300
Message-ID: <3f433185_at_softline.kiev.ua>
Date: Wed, 20 Aug 2003 11:30:43 +0300
Message-ID: <3f433185_at_softline.kiev.ua>
> 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));
I would only suggest to use date 31/12/9999 instead of NULL for the last
record in history (which is current).
The problem is that DBMS usually does not include NULL values in B-Tree
indexes. So, using this "infinite" date and indexes can speed up some
queries.
Received on Wed Aug 20 2003 - 10:30:43 CEST