Outside of the rational that adding a PK to tables simplifies changing a Key value, I was also told that numeric keys provide faster look-up then anything character based.
-----Original Message----- From: webguy [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:23 AM To: CF-Talk Subject: RE: DB Design The debate on primary keys is a long long long one. Some arguments are a) you shouldn't add columns to a table that are meaningless So why do you need a UserID column when you could use Username But what happens if a user changes his username,? you going to update all relation tables? b) using a auto ID in a db, means you are stuck with that db. (tightly coupled) This is a far argument in general. Many J2ee applications specify a DB independent key generating class. The method for generating this id is also source of many differing options, including : 1) use a UUID 2) use a table like objectname | nextid -------------------- customers | 12003 order | 50031 So you go get me the next customer id... 3) use high/low key generator http://castor.exolab.org/key-generator.html 4) use a mixture. B-4 is pretty good mention in my opinion, and which what I generally us in j2ee/Jboss, for example. Sometime in CF I just delegate this task to the DB. After that there are other issues such as the speed of indexing and searching by primary key, but these depend on DB server and datatypes... E.g. a int field lookup might be quicker than a varchar lookup.. A lot of people have opinions on this. Your best course of action is to know what the arguments are make your own choices for your app. A good place to find the discussions are in the Java/J2ee community, especially around the various persistance layers (CMP, castor, JDO and other Object-Relational mapping layers e.g. http://www.agiledata.org) due to their requirements of being DB independent. Some OO knowledge is helpful. my 2 cents. WG -----Original Message----- From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] Sent: 29 July 2003 15:48 To: CF-Talk Subject: SOT: DB Design I am working on a new DB design for a CFMX app and was doing a little refresher research on keys and data types and ran across this quote from former SQL Server project manager Ron Soukup, "Identity primary keys are for people who believe there's never time to design a table right but there's always time to do it over." In another related article, another MS SQL guy says that the only reason identity made it into SQL server was because of Access.... (not a direct quote). Anyone care to comment? Mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
