Re: Keeping historical data

>
> We are interested in how others have handled the issue of keeping
> historical data on tables. We have a corporate database on which we
> are implementing history. There are two primary methods of doing
> this:
>
> 1. Add an effective date (and possibly an end date and status)
> to the tables on which we want to maintain history. At
> the minimum the effective date would be added (low order)
> to the PK on this table.
> 2. Create new history tables and move the 'old' records
> there via triggers.
>
> We don't have as firm a list of requirements, but some of the
> requirements mentioned have been (in order of priority):
>
> 1. maintain accurate history of objects and relationships
> between objects
> 2. maintain good performance for current data
> 3. maintain good performance for historical data
> 4. minimize complexity of application coding
> 5. minimize complexity of changes to the db structure
> 6. minimize database space requirements (will be difficult
> just because of the nature of history)
> 7. allow flexibility for 'future' data
> 8. offline historical data
> 9. disaster recovery of current records before historical ones
>
> We have had a lot of discussion on the last three requirements and
> they may be eliminated.
>
> If you have implemented history, we would like to hear from you. Which
> method you used, how it's working, things that you would change the
> next time, general comments etc.
>

Some things to consider:

Adding a date that shows the last time a row was changed will not give accurate history.

If a single row is updated twice in one day, you will lose track of the changes made the first time. You will not know who made them or when.

It sounds as if you want to audit the tables. You may wish to check out the auditing features that Oracle as built in to the database.

If this is not for you, another way to do it is to use an 'after update' trigger on those tables you wish to audit.

The way I've done it is to duplicate the columns of the table to be audited in an audit table. Each regular table has 2 columns added, CHG_DATE, and USERNAME. These are updated by a 'BEFORE INSERT' trigger.

The audit tables have 3 columns in addition; AUDIT_PK, a generated primary key, AUD_ORACLE_USERNAME; it tracks who caused the audit row to be produced; AUD_DATE_MODIFIED, when was the audit record produced.

This may be overkill for some applications. We use it on tables where we must be able to accurately track changes.

As far complexity, the applications don't need know anything about the audit tables, as it is all cared for by triggers.

The only complexity is added work for the DBA.

I have scripts to automate the creation of these triggers if you are  interested.They are written in Perl ( not DBPerl, sorry ) and shell.

They are designed to get the table information from CASE 5.0 tables, but could be easily modified to read 'ALL_TAB_COLUMNS' instead.

( don't post a request for these to the list! )

Jared Still

jared_at_valleynet.com
73617.3036_at_compuserve.com Received on Fri Jan 26 1996 - 17:26:55 CST