> 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 - 03:30:43 CDT
