Re: creating audit tables

Have you thought about unifying the audit + the current table and add
from/to datestamps for every record?

Example:

from_dt   to_dt       value
9/1/2004  9/30/2004   ABC
9/30/2004 10/5/2004   XYZ
10/6/2004 12/31/9999  123

This would let you use the following query on the same table whether you
wanted historic values or current values.

SELECT * FROM table WHERE from_dt >= as_of_date AND to_dt <= as_of_date





Scott Cain wrote:

> Hi Ian,
>
> I created one audit table for each table in the database just because
> that seemed to me to be the sensible thing to do.  The reason we want
> audit tables is so that we can ask the question: "what was the state of
> the database 6 months ago" and the easiest way to answer that question
> is with shadow tables where I can write the same queries I do now, just
> changing (slightly) the table name and adding a date check to the where
> clause.  Using a big, unified table makes it much harder to ask that
> sort of question, unless you spend a fair amount of effort making views
> to simulate the real audit tables I already have.  I don't see any
> advantage to us in using a unified table.
>
> Scott