Re: problem with historical data and referential integrity

1. Not, it is very complex question without easy answers.
2. Troels Arvin is right about looking at the work of Rick Snodgrass. That book going out of print was a major loss to working programmers; thak god uyou can get it in PDF at his university website. But you cannot get the CD with all the code, done in assorted SQL dialects. Arrrgh!
3. Generally speaking, data warehouse type of stuff is done with an extra (start_time, end-time) pair in the tables. If you have a NULL in the end_time column, it is taken to mean that this is the current state of affairs.

You then use "CURRENT_TIMESTAMP BETWEEN start_date AND COALESCE (end_date, CURRENT_TIMESTAMP)" predicates in VIEWs for the current versions and the same kind of predicates for particular dates.

The bonus with this is that you can also set future changes. Are you a recratianl matha and logic fan? Ever see the "Grue and Bleen" problem in Scientific American puzzles column?

INSERT INTO Foobar (start_time, end-time, collor) VALUES ('2004-01-01', '2004-12-31', 'Blue'), ('2005-01-01', '2005-12-31', 'Green'); Received on Tue Dec 07 2004 - 21:09:14 CST