Heenan, Lambert
Lambert.Heenan at AIG.com
Wed Jun 2 16:18:34 CDT 2004
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