Charlotte Foust
cfoust at infostatsystems.com
Wed Jun 2 15:03:27 CDT 2004
Realistically, if you get a natural key corrupted in millions of records, what good does it do to avoid autonumbers? You still won't be able to reunite the child records, so a fear of autonumbers is patently ridiculous. A composite key is just as likely to corrupt, since it only takes one bad field to destroy it, and it's just as big a problem to recreate the information. Charlotte Foust -----Original Message----- From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com] Sent: Wednesday, June 02, 2004 9:30 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] OT: The Great Primary Debate No, I'd say that the answer to this unspoken question is that if they fear the corruption of the PK field then why on earth are they storing ANY information in a database at all? If one field can be magically corrupted then so can all the others. Lambert > -----Original Message----- > From: Lawhon, Alan C Contractor/Morgan Research > [SMTP: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 > -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com