Our product contains views of networks and inventory that are date-tracked so that you can wind the clock backward and see what the network looked like at a particular time.
Some of the lookup metadata used for validation is also date tracked because we re-used some existing tables.
I had a strange experience:
Run your test script, error saying something isn’t there. Check by typing in the SQL – it isn’t – OK. Log back in again, it is…
The test script was setting the effective date so that, when it end-dated some existing data the end-date was the one is should have been, not the system date. When updates on other data were being done the lookups weren’t there because they fell outside this range.
Deep joy. The only way you can tie temporal data together that I know of on modern databases is to use triggers and application logic – I’d be very interested in a generic way of doing this that allowed you to express range dependencies properly. On our current system this often only comes up on update, usually trying to end-date a parent with un end-dated children.
I think that there is a book on this but I can’t afford it right now and the people I work for wouldn’t buy it for us because 50p on a book that could save us thousands just isn’t the way they think…
At least I’ll know where to look first next time.