How to model disjoint date ranges?

From: Paul <pbrazier_at_cosmos-uk.co.uk>
Date: 17 Jul 2002 02:01:17 -0700
Message-ID: <51d64140.0207170101.301bd92f_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.

Currently the table is like:

CREATE TABLE foo (
  titleid int,
  startdate datetime,
  title varchar(20),
  CONSTRAINT PK_foo PRIMARY KEY (titleid, startdate)
)

INSERT INTO foo VALUES(1, '1900.01.01', 'AAA')
INSERT INTO foo VALUES(1, '2000.03.05', 'BBB')
INSERT INTO foo VALUES(1, '2001.05.22', 'CCC')

but I don't think this is normalised because in order to get the full information about the row you need to look at the other rows in the table with the same titleid to get the end of the date range. Also when joining this table a correlated subquery is needed. like:

SELECT * FROM bar
JOIN foo
  ON foo.titleid = bar.titleid
  AND foo.startdate = (SELECT Max(startdate) FROM foo

                       WHERE titleid = foo.titleid
                       AND startdate <= bar.actualdate)

Next thought is this:

CREATE TABLE foo (
  titleid int,
  startdate datetime,
  enddate datetime,
  title varchar(20)
)

INSERT INTO foo VALUES(1, NULL, '2000.03.04', 'AAA')
INSERT INTO foo VALUES(1, '2000.03.05', '2001.05.21', 'BBB')
INSERT INTO foo VALUES(1, '2001.05.22', NULL, 'CCC')
INSERT INTO foo VALUES(2, NULL, '2002.10.24', 'ZZZ')
INSERT INTO foo VALUES(2, '2002.10.25', NULL, 'YYY')

with maybe some constraint that forces the date ranges to be disjoint. But somehow this doesn't feel quite right either. Also we might have to have low and high values instead of NULLs for the dates if we define a primary key.

I'm not sure if it's a deficiency of my particular DBMS (SQL Server) but foreign keys can only be defined that are standard joins i.e. not "theta joins" (is this the right terminology?)

Is there a ideal way to do this?
If not, what are the relative merits of the options?

Paul. Received on Wed Jul 17 2002 - 11:01:17 CEST