I'd like to know if anyone can recommend how to best persist
relationships between 2 entities that only exist for a period of time.
For example, there is a relationship between a master account and a
sub-account. I want to know for any date, what the sub-accounts exist
for a specific master account.

Other requirements:
-Prefer a design that can persist a relationship between any 2
entities, i.e., one relationship table for all relationships in a db.
-Enforce multiplicity constraints

Thanks.

Jay A. Ritchie

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));

and a calendar table with all the enterprise temporal data in it --
holidays, fiscal calendar, etc.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
...);

And then write queries like this:

SELECT ...
FROM Foobar AS F1, Calendar AS C1
WHERE C1.cal_date BETWEEN F1.start_date
AND COALESCE (F1.end_date, CURRENT_TIMESTAMP)
AND ...;

The COALSECE() makes the data current and I can also pick ranges of
calendar dates.

But if you use '9999-12-31 23:59:59.99999' (this is ISO-8601 format
and the only one allowed in Standard SQL), then all your temporal math
gets harder. COALESCE(end_date, CURRENT_TIMESTAMP) is a small price
to pay for correct and most current information. Suddenly, you are
making a real date and time into Eternity and it is not.

I am not worried about the indexes because the key is PRIMARY KEY
(foo_id, start_date) and does not involve end_date at all. If I do
tree index on end_date, I think that most RDBMS products can locate
them all on one limb of the tree. Cetrtainly if I use hashing, they
all fall into the same hash bucket and I gain a huge boost in
searching.

As an aside, when I worked in COBOL systems in a State Prison System,
we had a field for "expected date of release" in the inmate records.
We would put in a real date (with a four digit year! We knew about
Y2K) or use '8888-88-88' for a "life Sentence" or '9999-99-99' for a
"Death Sentence" -- two very different kinds of unspecified times!!