[AccessD] OT: The Great Primary Debate

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






More information about the AccessD mailing list