I'm pondering an issue that I can best describe as database auditing vs. database history. Consider 3 tables:

Country CountryId PostalAddress PostalAddressId CountryId BankAccount BankAccountId PostalAddressId

Bank accounts have postal address that have country.

Now when a request to make a payment from an account comes in we need to store a snapshot of this hierarchy so that if an address changes for an account or more importantly the account number, we still have the address or account number at the time the payment was made for reporting purposes. So now we have:

PaymentHistory PaymentHistoryId BankAccountHistoryId BankAccountHistory BankAccountHistoryId PostalAddressHistory PostalAddressHistoryId CountryHistory CountryHistoryId

Another requirement is to provide an audit trail. I think the first inclination is to use the history table. Sounds like an obvious choice, but is it? I'm thinking this is mixing audit data with history data. When I see a foreign key relationship between non-history and history tables I feel that its wrong...

If a Country row changes, we should insert a row in the audit table but not the CountryHistory table, but should we insert a row in the history table(s) for every payment and fore go the logic required to check if the "current" history row is identical?

There is a clear separation in my mind between the history hierarchy I have here and auditing data. Have I just chosen bad nomenclature for what I'm calling the history hierarchy?

Have I explained the situation well enough? As I've typed this it seems obvious to me, but would love to hear some thoughts....

I would agree with you that Auditing and History are 2 seperate things.

History is driven by business logic - your need to track the address at the time that the payment was made.

Auditing is much more an automatic, application design driven process, the need to record specific changes to the database.

Having said that it does complicate your design keeping them seperate as you need a seperate audit and history table for each of you application tables.

Thanks for the post.

I've thought of an alternative to requiring a history table counterpart for each table in the hierarchy. What if I deny updates on all tables that would in the previous scenario have history table counterparts. Next add an "active" bit to the tables to signify the current row. By allowing only inserts to account for edits (maybe a trigger that flips the "active" bit for me) I can keep my FK relationships to existing records without the overhead of essentially a duplicate history schema and the entities that come with it. This is mixing history data with transactional data but is that necessarily a bad thing in this scenario?

Thoughts?

It can be done, but I never think it's a great idea.

You need to remember to query against the "active" version each time, although you can set up views to only filter the "active" data. Users/report builders/3rd parties also have the same issue.

The other problem is that each query references the "active" column, and because this only contains 2 values, indexing it does not gain you a great deal, unless it is part of the clustered key on the table - leading to a lot of table scans, which are not great for peformance.

The history table for each table is not a bad idea. Could the audit information be simplified into 1 table for the whole database ?

TableName ColumnName IdValue OldColumnValue NewColumnValue User DateTime etc...

Matt

Audit tables aren't a problem, I don't create entities for those anyway. Triggers create the rows and they are only accessed by reports.

I'm going the history table route. This creates a little more overhead but I don't see an easier way to preserve the history right now. It does provide the added benefit of making the transaction side considerably faster since queries for "unprocessed" records aren't mixed with years of old data...

One rule I am following is that all code tables are flattened out in the history schema. i.e. there are no history table counterparts for code tables...

Thanks for tossing around this idea with me.