Re: problem with historical data and referential integrity
From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 7 Dec 2004 19:09:14 -0800
Message-ID: <1102475354.576117.264050_at_f14g2000cwb.googlegroups.com>
Date: 7 Dec 2004 19:09:14 -0800
Message-ID: <1102475354.576117.264050_at_f14g2000cwb.googlegroups.com>
- Not, it is very complex question without easy answers.
- 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!
- 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 Wed Dec 08 2004 - 04:09:14 CET