In my experience, there are arguments for and against using ColdFusion UUIDs as database keys.
The most compelling for NOT using them are: 1. Where DB clients other than ColdFusion will need to create new records as it can be a bit of a pain to create consistent keys across the clients. 2. You are writing an extremely high performance application (Think millions of requests per day) and the extra overhead involved in having the keys generated by ColdFusion may outweigh the potential benefits. 3. You are writing an application that has to be finished in the next 10 minutes and you need to use the fastest method available to you. The most compelling reasons for using them are: 1. You know your keys are going to be unique if you ever need to merge databases. 2. When moving data between development, staging and live environments you don't need to worry about whether you will have duplicate keys, or if you need to run a script to 'remind' the database that some of your tables have autonumber fields. 3. A UUID identifies not only a unique record in a table, but in in the whole database, and for most purposes for any database. This is useful when exposing your data for multiple external systems, or rather when consuming data from multiple external systems. If the primary key is a UUID you don't need to keep track of the external primary key as well as your the one for your own database when performing updates from external data. 4. When using multiple different database types for a single application. If you use a ColdFusion UUID as the primary key you can keep a standardized way for dealing with identifiers regardless of the database. my 2c Spike >-----Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of >Geoff Bowers >Sent: Thursday, February 12, 2004 4:57 PM >To: CFAussie Mailing List >Subject: [cfaussie] Re: Whats your Primary Key? > >Phil Evans wrote: >> The way I interpret Ben's article, he has a leaning towards using >> identity style primary keys. > >I think Ben is saying he leans towards DB specific coding to >take advantage of whatever databases enterprise features -- >and in this specifies using identities specific to each >dbtype. His statement about >CreateUUID() is to my mind ridiculous -- but perhaps the >article puts it out of context. > >> It's what I run with for some fairly complex db designs, and I've >> found them easy, convenient and effective. > >I suspect that these designs using identity would not easily >migrate from one DB to another, nor would the data have any >hope of being migrated from one DB platform to another >(without the use of migration scripts etc). However, dbtype >migration is not really an issue for most. > >I'm not entirely sure why UUIDs as keys would have any sort of >impact at all on db design. The only impact I can see is with >data storage and index efficiency -- but then I'm not really a DBA. > >-- geoff >http://www.daemon.com.au/ > > >--- >You are currently subscribed to cfaussie as: >[EMAIL PROTECTED] To unsubscribe send a blank email to >[EMAIL PROTECTED] > >MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia >http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
