Re: Handling "as-of" data in relational database design
Originally posted by drewj840
Please see the Word file attachment for a description of the problem.
Another option would be to have an audit history table for each table, maintained by triggers. The history table would have the current record and all its previous versions, the main table just the current. "As of" reporting would be based on history tables.
There is a distinction to be made between history of corrections to data (audit trail), and valid data that has effective date ranges. For example, with employee salaries you would normally keep all the valid history in the main table e.g.:
I would not want to keep the "current" salary in the employee table, or flag a particular salary as "current", because you are then relying on some background process to redefine the "current" value as the appropriate moment passes. In any case, I don't consider it a particularly useful piece of data. For example, on 01/04/2003 if I am calculating an employee's pay for March, I need the salary effective on 31/03/2003, NOT the "current" salary. If there was a demand for it, I would define a view like:
CREATE VIEW current_salaries AS
SELECT * FROM salaries
WHERE SYSDATE BETWEEN from_date AND NVL(to_date,SYSDATE);