Stacy, Nice approach to accelerating retrieval. Thanks to you both.
Andy -----Original Message----- From: Stacy Young [mailto:Stacy.Young@;sfcommerce.com] Sent: Thursday, October 24, 2002 11:49 AM To: CF-Talk Subject: RE: Good Database Practices A slight variation to this approach would be as follows: Make the primary key a combination of two fields. Say for example a UUID column uniquely identifying each record and an orderID column (non-unique). A third column called a THRU_DATE would function as the identifier to determine which record is the active one. For instance, when an order is created a record is entered with a status of PENDING. At this point the thru_date is NULL because it is the active record. When the status of the order is changed a new record is inserted but first the original entry (searching by ORDERID) is updated in that the thru_date is supplied a timestamp signifying it's being "retired" as the active record. When querying the database simply select all records with a thru_date of null and you'll retrieve the data you're after...and if you need to audit, simply leave out the thru_date and you'll bring up that same order with every record detailing it's lifecycle and at which date range each state existed. Hope that helps! Stace -----Original Message----- From: Samuel Neff [mailto:sam@;serndesign.com] Sent: Wednesday, October 23, 2002 11: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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
