MartyConnelly
martyconnelly at shaw.ca
Wed Jun 2 19:58:03 CDT 2004
This article has some test results and workarounds using SQL Server which may or maynot reinforce your reasons for using autonumbers. The Cost of GUIDs as Primary Keys http://www.informit.com/articles/article.asp?p=25862 Heenan, Lambert wrote: >Ah Hah! This sounds like a cunning variation on "show them what the want to >see, but use an AutoNumber in any case". > >I like it! :-) > >Lambert > > > >>-----Original Message----- >>From: Porter, Mark [SMTP:MPorter at acsalaska.com] >>Sent: Wednesday, June 02, 2004 5:04 PM >>To: Access Developers discussion and problem solving >>Subject: RE: [AccessD] OT: The Great Primary Debate >> >>One approach I've seen which I've adopted is a combination of both. >> >>Each table has both a unique identifier as well as a natural key. The >>unique identifier is always the FK and has the clustered index. Great for >>joins and row identifiers. >> >>The natural key has a unique index on the fields preventing duplicates. >> >>Mark >> >> >> >> >>>-----Original Message----- >>>From: Charlotte Foust [mailto:cfoust at infostatsystems.com] >>>Sent: Wednesday, June 02, 2004 12:15 PM >>>To: Access Developers discussion and problem solving >>>Subject: RE: [AccessD] OT: The Great Primary Debate >>> >>> >>>I respectfully disagree. <G> >>> >>> Those who prefer autonumbers are in favor of simplicity. If you've >>>ever designed databases using a design tool like ERWin or Visio or any >>>other tool for designing the data/entity structure, you >>>quickly discover >>>what a mess compound keys can be. In Access table design, it >>>looks like >>>you're just creating a link between comparable fields in two tables. >>>When you use a design tool, you get a different take on it. >>>Visio 5 and >>>before only allowed you to create unique field names in a database >>>structure. That meant that if you had ABCID in one table, >>>you couldn't >>>create it in another, so it you wanted it there as a foreign key, you >>>created a relationship and the tool inserted the field in the other >>>table. However, if it's part of a unique key but not in >>>itself unique, >>>you can't enforce RI on just that specific field, so you wind up >>>dragging *all* the fields in the compound key to the other table to >>>create the relationship. If that key happens to be part of >>>the primary >>>or unique key in the other table, then you wind up with an even bigger >>>key to connect to some other table. It starts to snowball and you can >>>wind up with 6 or 7 fields in a PK, which is ridiculous. >>>Those who opt >>>for "natural" keys, want a key they can look at and recognize >>>conveniently. No one in their right mind is going to do that on >>>millions of records voluntarily when something goes wrong, so where's >>>the advantage? >>> >>>Charlotte Foust >>> >>>-----Original Message----- >>>From: Mitsules, Mark S. (Newport News) [mailto:Mark.Mitsules at ngc.com] >>>Sent: Wednesday, June 02, 2004 5:07 AM >>>To: 'Access Developers discussion and problem solving' >>>Subject: RE: [AccessD] OT: The Great Primary Debate >>> >>> >>>As a potential hypothesis, I would have to agree. But in >>>order to prove >>>your hypothesis you should have presented the opposite "Fear >>>Factor" as >>>well:) That those in favor of AutoNumbers fear the user's ability to >>>screw up even a five-field compound key by unwittingly uncovering the >>>one situation in which it would fail;) And, that those in favor of >>>AutoNumbers fear the complexity and never-ending maintenance that will >>>"inevitably" result from such a decision;) >>> >>> >>>Mark >>> >>> >>> >>>-----Original Message----- >>>From: Lawhon, Alan C Contractor/Morgan Research >>>[mailto:alan.lawhon at us.army.mil] >>>Sent: Wednesday, June 02, 2004 8:51 AM >>>To: 'Access Developers discussion and problem solving' >>>Subject: RE: [AccessD] OT: The Great Primary Debate >>> >>> >>>Martin, Susan, John, Jim, Charlotte, Drew, Gustav, et al: >>> >>>I think there is another factor involved in this "AutoNumber versus >>>Natural Key" PK debate. For lack of a better word or terminology, I'm >>>going to refer to it as the "Fear Factor" or a fuzzy type of >>>generalized >>>apprehension. This "apprehension" boils down to something along the >>>lines of, "Well, what happens if the AutoNumber field gets >>>corrupted or >>>somehow those autonumbers get jumbled or out-of-sequence? If that >>>happens, then how do we re-establish the primary keys and make sure >>>they're associated with the correct records?" (The more records there >>>are in a table, the more heightened this fear or apprehension tends to >>>be.) >>> >>>The answer to this [unspoken] question is that they would rather avoid >>>the possibility altogether by using non-Autonumber composite (i.e. >>>"Natural") primary keys. It appears, from my experience, >>>that folks who >>>have not been trained in database theory seem to have an intuitive >>>preference for natural keys - even when such "natural" keys >>>involve the >>>concatenation of two (or >>>more!) fields - with all the headaches that come from trying to manage >>>such an unweildy arrangement. >>> >>>I have experienced this issue firsthand here at work. We are >>>managing a >>>substantial (several million record) environmental database with >>>multiple linked tables, numerous views, action queries, macros, et >>>cetera ... This application requires primary and foreign >>>keys in nearly >>>all of the base tables. Early on we tried to persuade the senior >>>project engineer, (a chemical engineer by profession), of the >>>wisdom of >>>using single-field AutoNumbers for the PK in the most >>>important table of >>>the application. He adamantly refused, insisting on a two-field >>>composite primary key. We did our best to try and persuade him that an >>>autonumber PK was best, but he wouldn't hear it. Since this engineer >>>has major input into our performance appraisals, the programming staff >>>acquiesced. We decided to live with a "composite" PK - even if we >>>didn't like it or agree with it. >>> >>>After giving this a great deal of thought, I have come to the >>>conclusion >>>that folks who prefer "natural" (composite) PKs do so due to a general >>>apprehension or mistrust of AutoNumbers. I don't think it is a >>>"technical" issue, but rather a "people issue" centering >>>around fear and >>>apprehension. Whenever one sees a strong visceral reaction, (such as >>>what Martin saw yesterday), this tends to reinforce my perception that >>>this is more of a "people problem" than a purely "technical" problem. >>> >>>Does this make any sense to the rest of you? >>> >>>Alan C. Lawhon >>> >>> >>>-----Original Message----- >>>From: Martin Reid [mailto:mwp.reid at qub.ac.uk] >>>Sent: Tuesday, June 01, 2004 1:17 PM >>>To: Access Developers discussion and problem solving >>>Subject: [AccessD] OT: The Great Primary Debate >>> >>> >>>I was taking day one of a 4 day Programming SQL Server 2000 course >>>today. 8 Oracle programmers moving to SQL Server, 6 of our Ingres >>>programmers moving to SQL Server. >>> >>>Came to the section on Table Design. I said use an Identity value for >>>the PK on the table - all h%ll brooke loose for the next hour as the >>>great debate happened live in person. Pity JC wasnt there to >>>back me up >>>(<: Was split between the younger developers who supported the use of >>>the ID column and the older developers and DBAs who use natural keys. >>>Almost a 50//50 split on age lines maybe reflecting different >>>attitutes >>>to design. Took me about 20mins with one of the older guys to explain >>>how the relationship was maintained using Idt IDs as opposed to his >>>staff number. He seemed to have real problems getting the concept. >>> >>>Martin >>> >>> >>>-- >>>_______________________________________________ >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>>-- >>>_______________________________________________ >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>>-- >>>_______________________________________________ >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>>-- >>>_______________________________________________ >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>> >>> >>> >>************************************************************************** >>********* >>2/6/2004 >>This transmittal may contain confidential information intended solely for >>the addressee. If you are not the intended recipient, you are hereby >>notified that you have received this transmittal in error; any review, >>dissemination, distribution or copying of this transmittal is strictly >>prohibited. If you have received this communication in error, please >>notify >>us immediately by reply or by telephone (collect at 907-564-1000) and ask >>to >>speak with the message sender. In addition, please immediately delete this >>message and all attachments. Thank you. ACS >> >> >>-- >>_______________________________________________ >>AccessD mailing list >>AccessD at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/accessd >>Website: http://www.databaseadvisors.com >> >> -- Marty Connelly Victoria, B.C. Canada