[AccessD] OT: The Great Primary Debate

Arthur Fuller artful at rogers.com
Thu Jun 3 20:50:07 CDT 2004


It's actually more complicated than that, and while I fall on the side
of ANPKs, I do respect the opinions of such luminaries as Joe Celko, who
despises ANPKs. His reason? They model nothing in the real world; his
premise -- any data model that artificially maps real-world entitities
is by defnition flawed.

He works in different environments than I. Typically I think of the
egg-carton, and question the significance of which two eggs I choose to
make my morning omelet. He thinks in terms of "this alternator was
manufactured by XYZ for use in Ford models T, U and V. It was
manufactured on a day in a factory and signed off by employee ABC as
valid and working. Or to put his argument another way, I am a vendor of
antiques and I have precisely one Queen Anne male chair (the difference
between male and female being the armrests or lack thereof), which was
hand-crafted by Hortense Witherspoon circa 18whatever. 

If I read him correctly (which, given my rapidly increasing senior
moments, may be a faulty assumption), these cases (as opposed to the
eggs in a carton) can all be given primary keys which derive from the
data themselves, and do not falsify the picture by introducing an ANPK.

I don't mean to resurrect the PK debate, nor to say that I agree with
Joe. Concerning the latter, I emphatically do not agree. But he is a
luminary that has written some brilliant books and probably makes twice
the money that any three of us on this list do. Oracle and DB/2 seem to
be his favorite turf, but I could be wrong about that part.

Anyway, I stand clearly on the side of ANPKs and have found none of
Joe's arguments on this subject persuasive. But as he wrote to me in an
email a while back, "Go take a data-modeling course." Well, I took his
advice and took a data-modeling course, and I remain unpersuaded. 

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules,
Mark S. (Newport News)
Sent: Wednesday, June 02, 2004 9: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




More information about the AccessD mailing list