Sam, Would it be more effective given the hopefully minimal usage of the audit trail to a flat file and save XML version of the record (in case database structure changes). I would think that this would add less overhead then a db Insert/Add.
Andy -----Original Message----- From: Samuel Neff [mailto:sam@;serndesign.com] Sent: Wednesday, October 23, 2002 10:04 PM To: CF-Talk Subject: Re: Good Database Practices Andy, That's a good question. Here's my $0.02. I do not think it is necessary or even helpful to store aggregate information as a checkpoint to validate data. What is often done, however, is to keep an entire audit trail for the database. Basically what this means is you always keep a copy of every record. There are a few ways to do this. One way is to add modified date and active fields to every table, and then instead of modifying any record you always insert a new record and update the modified date. To delete a record, insert a new date with latest modified date and set active equal to false. The major drawback of this method is that all joins have to filter out on only the latest record and you end up with very slow queries. However, this is a very common practice in accounting systems (PeopleSoft comes to mind since I deal with this regularly and curse it almost every day). Another method is to have a Audit or History version of every table. Use triggers so any time a record is change a new record is added to the Audit table so you have a complete history of the record. The advantage here is much greater query performance, but of course updates/inserts/deletes are slower. Does this tradeoff sound familiar? Same considerations as with indexes.. Speed up reads, slow down writes. Which method is best will vary by app and circumstances. HTH, Sam Date: Wed, 23 Oct 2002 21:07:11 -0500 From: "Andy Ousterhout" <[EMAIL PROTECTED]> Subject: Good Database Practices Message-ID: <[EMAIL PROTECTED]> How important is it to build auditing into your database? For example, in an invoicing system where you have an invoice table and an invoice item table, does anyone recommend keeping totals such as number of items or total invoice amount in the invoice record? Not for reporting, but for data integrity checks. If so, how often would you validate the tables and what do you do if errors are found? Andy ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
