Re: problem with historical data and referential integrity

Thanks to you and others for interesting answers.

I have thought for a long time that DB vendors should make some extensions to accomodate the time aspects; your remarks/pointers have confirmed my belief. Now my question is, what is to do with current and given state of affairs, i.e. having to use Oracle or DB2?

Should one just give up on RI and add valid_from/to columns to many tables?

Personally I tend to just separate our database in two: one set of tables containing only current data with full and simple RI implemented. All historical data goes (for example via a trigger on update/delete) into separate "_hist" tables with additional valid_from/to columns and without referential integrity. Typical reporting that might need to access the historical data would access the _hist tables read-only, and the more transactional work operates on the "current" tables.

What do you think?

-- 
Peter Mutsaers, Dübendorf, Switzerland.