point-in-time vs. shadow tables for audit history

[Emin]

Dear Experts,

We need to design a database which has an audit trail for updates to a
certain set of tables. The two main approaches I've seen for this
include shadow tables (an extra table to track changes to the main
table) and point-in-time architectures (having an "event date" and an
"as known" date for every row). Can someone suggest the pros and cons
of each approach or point me to relevant literature discussing the
best approach?

To provide an example of what we are aiming for, imagine we have a
single table with date, value pairs, e.g., [(1/1/2000, 5.2),
(1/1/2001, 5.3), etc.]. Sometimes we may need to go back and correct
old values, e.g., on 1/1/2008 we may realize that the value on
1/1/2001 really was 4.2 not 5.3. The main use case is we want to get a
time series of most up-to-date date/value pairs. A secondary use case
is getting a time series of what we thought at a certain point in the
past. The shadow table approach would have an extra table to track all
changes while the point-in-time approach would an an event_date and
as_known_date column to the original table.

Thanks,
-Emin

[Erland Sommarskog]

Emin (em**********@g mail.com) writes:

We need to design a database which has an audit trail for updates to a
certain set of tables. The two main approaches I've seen for this
include shadow tables (an extra table to track changes to the main
table) and point-in-time architectures (having an "event date" and an
"as known" date for every row). Can someone suggest the pros and cons
of each approach or point me to relevant literature discussing the
best approach?

If all you have a column within the table, all you can track is the most
recent change, which is a fairly lightweight audit, and certainly not a
trail.

As for shadow tables, there are different ways to go. You can indeed
have a shadow table which is more or less a copy of the original, but
you could also have a generic table that has the keys (tablename,
keyvalue1, keyvalue2, ...), the data about who and when from what,
and then two XML columns that holds the before and afterimages of
the data. This information is more difficult to query, and not to
recommend if you need to retrieve the historic situation outside audit
trail. On the flip side, it's easy to write a generic function that
displays the changes for a given entity.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

Here is one article discussing different design options for auditing:
http://www.mssqltips.com/tip.asp?tip=1468

On SQL Server 2008 Change Data Capture (CDC) provides a convenient
mechanism to implement data auditing:
http://www.sql-server-performance.co...apture_p1.aspx

Also, CDC implements DDL auditing as well:
http://www.sql-server-performance.co...apture_p1.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Plamen Ratchev (Pl****@SQLStud io.com) writes:

On SQL Server 2008 Change Data Capture (CDC) provides a convenient
mechanism to implement data auditing:
http://www.sql-server-performance.co...apture_p1.aspx

Also, CDC implements DDL auditing as well:
http://www.sql-server-performance.co...apture_p1.aspx

But keep in mind that CDC is primarily designed and intended for different
purposes than auditing.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

> Can someone suggest the pros and cons of each approach or point me to relevant literature discussing the best approach? <<

Google up a series of articles by Tom Johnston in RM REVIEW.

My other advice is to buy a third party tool that has some legal
standing and NOT write your own.