[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