jwcolby
jwcolby at colbyconsulting.com
Wed Jun 2 16:14:32 CDT 2004
Yea, I tend to agree with you. In fact during the great debate that was one of the "arguments" for using natural keys... What if ... I can always look at the data and see... John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawhon, Alan C Contractor/Morgan Research 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