> -----Original Message----- > From: Matt Williams [mailto:[EMAIL PROTECTED] > Sent: Monday, April 07, 2008 11:49 AM > To: CF-Talk > Subject: Re: Can you guys give me your thoughts on this? > > This type of data logging is quite common. One way to do it is before > doing the update, get the record again from the db. Compare it to the > form data. Another way is to have the form send a duplicate set of > form fields named something like "orig_productName". Or you could even > be fancy with some javascript to make a list of the fields changed > when they are changed. > > For tracking what was changed, you don't really need the XML, you can > do it with an additional table. You could have a table with something > like > changed_ID | changed_table | changed_field | orig_value | new_value. > A sample record would be > 1234 | 'tbl_product' | 'product_name' | 'my widget' | 'my cool widget'
I've done this before using the same table. Basically I added two new columns: EditDate (the date of the change) and EditUser (the user ID of the one making the change). When somebody updates something you save a new row with the updated data - then select it using a MAX date. Deletes can be handled in two ways: 1) You can just plain delete things. Done and done - but you lose the audit trail. 2) You can add another column, a boolean - something like "Active" or "Deleted". Only "active" records should be shown - the rest would be considered "deleted". The main issue with this is that it's a waste of space - even a small change will result in a duplicate of all information. This is fine for small databases (storage is cheap) and it does make things easier. Going back to an older version is as easy as copying a row and setting a date. Matt's system above is much better on storage although it has a shadow of the same problem. The most commonly edited fields tend to be the larger ones (descriptions for example) and these will be double stored as well - in fact for them (since you're saving a "before and after" instance for each field) the total space usage might end being the same as my version depending on the data you're talking about. Really the only way to truly optimize storage would be to implement a base "difference" system like they do in high-end source control. This would be complicated, require more processing and would be generally "icky" (that's a technical term). This seems like enormous overkill unless your database is so large that storage costs are an issue. One major advantage to Matt's, however, is that it can be "bolted on" to an existing system more easily - although the process for storing and recovering edits is more complex no changes are needed to the core system. In my case everything that accesses the tables will need to understand to grab only the latest version. There are probably dozens of variations here - the size of your data, the frequency of updates, the traffic, etc - that's going to determine what you want to do. Jim Davis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302895 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
