Re: Audit Table Strategies

Michael Krantz wrote:

>We need to audit all updates to numerous tables. We will
>have a companion audit table for each audited data table.
>(We could probably make a single audit table work, but don't
>want to.) The audit tables will be managed by triggers on
>the data tables.
>
>Table A (PK_COL, DATA_COL_1, DATA_COL_2, LAST_UPD_USR, LAST_UPD_TS)
>
>I am considering the following two formats:
>
>Table A_AUD (UPD_USR, UPD_TS, AUDIT_FUNC[I,U,D] not null, PK_COL_AUD not
>null,
> DATA_COL_1_OLD, DATA_COL_1_NEW, DATA_COL_2_OLD, DATA_COL_2_NEW,
> PK(UPD_USR, UPD_TS))
>
>or
>
>Table A_AUD (UPD_USR, UPD_TS, AUDIT_FUNC[I,U,D] not null, PK_COL_AUD not
>null,
> DATA_COL_NAME, DATA_COL_OLD varchar2, DATA_COL_NEW varchar2,
> PK(UPD_USR, UPD_TS, DATA_COL_NAME))
>
>The first has a primary key of timestamp and user (timestamp
>alone would probably work), operation == update/insert/delete,
>the primary key of the record being audited, and the previous
>and new value for each column in the audited record. Obviously,
>new values are null on delete and old values are null on insert.
>This for each of many tables, twice duplicating each data column.
>
>The second approach has one old-value column and one new-value
>column in the audit table, with the column name added to the key.
>So, there is a record for each column updated in the record being
>audited, with no audit record for columns that are unchanged. This
>includes null-in and null-out, so null columns aren't audited on
>insert or delete. In this case, all data is converted to text,
>and the type of the data columns is varchar2(longest column).
>In our case, the longest columns is only about 250 for one table
>and 100 or less for all others.
>
>I am interested in preferences and trade-offs between these two
>strategies and suggestions for improvements and alternatives.
>Also ideas for storage strategy; we will probably just create
>a separate tablespace for all audit tables.
>
>The data tables are relatively small and relatively static in size;
>some are also static in content. In addition we will have log tables
>that store continuous streams of data; these will not be audited and
>will also require separate tablespaces. The logs will require an
>archive/purge strategy; the audits will probably be kept on-line.
>
>I could not find any written material on audit strategies, but
>such pointers would also be welcome.
>
>

If I understand what you are doing my preference would be no constraints whatsoever and no
use of old/new values as that is redundancy without purpose. The newer value is always accessible.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)