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
