Re: creating audit tables

Oh yes. I do that a lot for attributes that need a history (last name,
which changes when you get married, etc) It is a bit more complicated
for queries though, since I use null to indicate an unknown end date
instead of the Y2K problem solution below.

-Ian

>>> William Yu <wyu(at)talisys(dot)com> 10/15/04 12:46 PM >>>
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster