As Andy has been saying I also would highly recommend that you create an auto numbered primary key. Just because this field is your primary key does not mean that it has to be the field to link your tables together. But having this key makes it very easy to update a single row in your table.
In the future as you add/modify tables you can make this primary key a foreign key in your other tables. We have had databases designed that use both compound keys and unique numbers. The systems that use unique numbers always seem to perform faster and easier. And honestly every time we have defined a case where a combination of fields should be unique we've ended up with a situation where we needed to add a duplicate for some reason. Just my humble $.02 worth. ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: Andy Ousterhout [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 07, 2002 10:13 PM > To: CF-Talk > Subject: RE: Primary Keys & Duplicate Values > > > I have the same issue in that I have a web system that I am > integrating with > my Order Management/Mfg system. The Order Mgmt uses Product Number as > primary key, my web system does not. The advantage that I > have is that my > Order Mgmt System enforces the unique Product Number Key. In > your example, > if you have purchased items as well, then you can definitely > have duplicate > item numbers, but as you stated, item number+mfg id should be > unique. I > would use this to match the two systems together, but not at your new > system's key. I would have only 1 product table for both > sold as well as > purchased items, work in progress and mfg items. > > Just my two sense (or lack of sense as some more experienced > DB designers > might say -- let me know since I am designing my next release.) > > Andy > > -----Original Message----- > From: Srimanta [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 07, 2002 4:03 PM > To: CF-Talk > Subject: Re: Primary Keys & Duplicate Values > > > Thanks. > Yes I understand. > What I could do is to combine both modelno and the name of > the manufacturer > and use them in updates. That should prevent updating the > wrong records > should the model number not be unique. > The problem with using an additional field (perhaps with auto > number) is > that the table to be updated > and the table from which the new values (price etc) will be > sourced may not > have the same auto number id. Also my table(Table1) will have > product data > from multiple vendors. > Different vendors will have tables where Id values might clash. > > Srimanta > ----- Original Message ----- > From: "Andy Ousterhout" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Tuesday, October 08, 2002 8:34 AM > Subject: RE: Primary Keys & Duplicate Values > > > > I highly recommend that you don't make Model number your > primary key. Add > a > > new field, numeric, and make your primary key a meaningless > number that > will > > never change. I've used several products that lock model > number and for > > newer companies where their numbering system evolves, this > has been an > > incredible and preventable pain in the back side. > > > > What does everyone else recommend? > > > > Andy > > > > -----Original Message----- > > From: Srimanta [mailto:[EMAIL PROTECTED]] > > Sent: Monday, October 07, 2002 1:48 PM > > To: CF-Talk > > Subject: Re: Primary Keys & Duplicate Values > > > > > > Thanks > > Field 1 represents model number of products which should be unique. > However > > due to some error in data entry some of the records are > duplicate. I can > > safely delete those records. > > > > Srimanta > > ----- Original Message ----- > > From: "Robertson-Ravo, Neil (REC)" > <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Monday, October 07, 2002 10:52 PM > > Subject: RE: Primary Keys & Duplicate Values > > > > > > > Yep, your problem is that you have dupes in the column > you want to tag > as > > a > > > PK. is Field 1 the only field which is uses dupe > values? are the > > records > > > technically unique or can you safely delete them? > > > > > > > > > > > > -----Original Message----- > > > From: Srimanta [mailto:[EMAIL PROTECTED]] > > > Sent: 07 October 2002 10:43 > > > To: CF-Talk > > > Subject: OT: Primary Keys & Duplicate Values > > > > > > > > > Hi, > > > Once again its me. > > > > > > I have a table with 18000 records. > > > There are three fields say field1, field2 and field3. > > > There are no primary keys at the moment. > > > > > > I want to delegate field1 as the primary key in the > modified table. > > > When I try to create field1 as the primary key, an error > is generated as > > > there are duplicate values in the records in field1. > > > How do I find which values are duplicate in field1. > > > I cannot use the Find and replace function as I do not > know which values > > to > > > look for. Also manually it is impossible as there are too > many records > to > > > search through. > > > Is there a Cold Fusion custom tag or function or SQL > syntax I can use? > > > Any help will be much appreciated. > > > > > > Thanks > > > Srimanta > > > ----- Original Message ----- > > > From: "Kola Oyedeji" <[EMAIL PROTECTED]> > > > To: "CF-Talk" <[EMAIL PROTECTED]> > > > Sent: Monday, October 07, 2002 9:38 PM > > > Subject: RE: Variable locking > > > > > > > > > > Hi > > > > > > > > I'm joining this thread late. Can I just confirm what > you guys are > > > > saying: In CFMX named locks should be used in place of > scoped locks > and > > > > locks are only needed > > > > When a possible race condition could occur? > > > > > > > > Thanks > > > > > > > > Kola > > > > > > > > -----Original Message----- > > > > From: Sean A Corfield [mailto:[EMAIL PROTECTED]] > > > > Sent: 04 October 2002 22:53 > > > > To: CF-Talk > > > > Subject: Re: Variable locking > > > > > > > > On Friday, Oct 4, 2002, at 12:07 US/Pacific, Gaulin, Mark wrote: > > > > > Actually, that using NAME is not a better practice... > the SCOPE > > > > > attribute is > > > > > safer and is also what MM support advised us to use (when > applicable). > > > > > > > > Pre-MX. > > > > > > > > > Sure, the scope of a NAME-based lock will be tighter > than using > SCOPE, > > > > > > > > > but > > > > > SCOPE will be safer and, as a bonus, you can use CF > 5's (and prior) > > > > > auto-checking for missing locks... > > > > > > > > Which is no longer available in MX because it is no > longer needed. > > > > > > > > > Basically, "NAME" is older than "SCOPE", and SCOPE > was added to > > > > address > > > > > issues that NAME cannot handle. > > > > > > > > SCOPE was added to resolve bugs in earlier releases of > CF around the > > > > shared scope memory corruption problems. That is no > longer an issue in > > > > CFMX. > > > > > > > > An Architect's View -- http://www.corfield.org/blog/ > > > > > > > > Macromedia DevCon 2002, October 27-30, Orlando, Florida > > > > Architecting a New Internet Experience > > > > Register today at http://www.macromedia.com/go/devcon2002 > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
