Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Handling "as-of" data in relational database design

    Please see the Word file attachment for a description of the problem.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    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.:

    empno,from_date,to_date,salary
    123,01/04/2002,31/03/2003,10000
    123,01/04/2003,,11000

    However, if I now realise that the salary in the last record should have been 12000 from 01/04/2003 the corrected data is:

    empno,from_date,to_date,salary
    123,01/04/2002,31/03/2003,10000
    123,01/04/2003,,12000

    If you want to know what the salary was (incorrectly) thought to be yesterday, that is where I would use a separate audit table, which would now look something like this:

    empno,from_date,to_date,salary,edit_date
    123,01/04/2002,31/03/2003,10000,01/03/2002 12:45:00
    123,01/04/2003,,11000,01/04/2003 13:44:32
    123,01/04/2003,,12000,24/04/2003 17:28:00

    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);

  3. #3
    Join Date
    Apr 2003
    Posts
    3

    Thumbs up

    Excellent suggestions. Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •