Re: Good Database Practices
Andy Ousterhout wrote: 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. You do realize that you lose atomicity with this option? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Good Database Practices
In such a situation ( Invoice and InvoiceItems and other tables that trickled down to a price table shared with products, etc... ) I had a problem where the price of a product would change and past invoices would change. So, for that reason I always recommend storing the current price (or PriceID) in the InvoiceItems table. As far as worrying about the total invoice amount and number of items. I see no reason to store it separately in the Invoice table, unless you wanted to try to avoid doing calculations and (potentially) extra database queries every time you needed these two numbers. I mean, computers don't do things they aren't told to do. If the code is perfect, the numbers will always match up. At 09:07 PM 10/23/2002 -0500, you wrote: 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 -- Jeffry Houser | mailto:jeff;farcryfly.com DotComIt, Putting you on the web AIM: Reboog711 | Phone: 1-203-379-0773 -- My CFMX Book: http://www.amazon.com/exec/obidos/ASIN/0072225564/instantcoldfu-20 My Books: http://www.instantcoldfusion.com My Band: http://www.farcryfly.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: Good Database Practices
Jochem, What do you mean by atomicity? By the way, how do you pronounce your name? wah-keem accent on the first part? Andy -Original Message- From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl] Sent: Thursday, October 24, 2002 3:14 AM To: CF-Talk Subject: Re: Good Database Practices Andy Ousterhout wrote: 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. You do realize that you lose atomicity with this option? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Good Database Practices
Agree. Each invoice and related Invoice Items records need to be independent of customer and product tables for the reasons you stated below. Has anyone designed a separate data structure for reporting on customer and item sales? I am curious if anyone has found this to be necessary or if reporting requirements did not negatively affect production database response times. Andy -Original Message- From: Jeffry Houser [mailto:jeff;farcryfly.com] Sent: Thursday, October 24, 2002 6:35 AM To: CF-Talk Subject: Re: Good Database Practices In such a situation ( Invoice and InvoiceItems and other tables that trickled down to a price table shared with products, etc... ) I had a problem where the price of a product would change and past invoices would change. So, for that reason I always recommend storing the current price (or PriceID) in the InvoiceItems table. As far as worrying about the total invoice amount and number of items. I see no reason to store it separately in the Invoice table, unless you wanted to try to avoid doing calculations and (potentially) extra database queries every time you needed these two numbers. I mean, computers don't do things they aren't told to do. If the code is perfect, the numbers will always match up. At 09:07 PM 10/23/2002 -0500, you wrote: 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 -- Jeffry Houser | mailto:jeff;farcryfly.com DotComIt, Putting you on the web AIM: Reboog711 | Phone: 1-203-379-0773 -- My CFMX Book: http://www.amazon.com/exec/obidos/ASIN/0072225564/instantcoldfu-20 My Books: http://www.instantcoldfusion.com My Band: http://www.farcryfly.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Good Database Practices
Andy Ousterhout wrote: Jochem, What do you mean by atomicity? Atomicity, from the ACID test :) http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci213756,00.html By the way, how do you pronounce your name? wah-keem accent on the first part? Like the German Jochen. Sorry, I don't know any phonetic alphabet. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Good Database Practices
Thanks. I'll ask my daughter, she is taking German. -Original Message- From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl] Sent: Thursday, October 24, 2002 8:59 AM To: CF-Talk Subject: Re: Good Database Practices Andy Ousterhout wrote: Jochem, What do you mean by atomicity? Atomicity, from the ACID test :) http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci213756,00.html By the way, how do you pronounce your name? wah-keem accent on the first part? Like the German Jochen. Sorry, I don't know any phonetic alphabet. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
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=listsbody=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
RE: Good Database Practices
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=listsbody=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
RE: Good Database Practices
I'd add to this with: If you have a lot of data and most of your views only need the current data, you could also use a separate table for historical data and simply copy the current record to a new record in the history table for each update. This way you don't have to filter on the null, you just have to perform an insert with each update. It also allows you to avoid the UUID if you like, but that's very implementation specific and I won't get into all the pros and cons of using UUID's here. My tendancy is to use stored procedures for most inserts and updates, so in my case adding the historical record with each update would be a near trivial addition to the codebase. If I wanted to be really slick about it, I'd compare the current update against the last update and only add a new record if the last update was performed by a different user or on a different date, i.e. If a user updates the record once, then a 2nd time 60 seconds or so later, it would only have one entry, because from the users' perspective they really only updated it once -- plus it cuts down on data inflation. If that last update was performed by the same user within a short period of time, you just update the date on the history record. Of course, again, it does inflate the update process / procedure, however, updates invariably occur with much less frequency than views, so it's liable to be more efficient over-all. S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 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
Good Database Practices
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=listsbody=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
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=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Good Database Practices
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=listsbody=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
