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 <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
