[AccessD] OT: The Great Primary Debate

Jim,

You are misstating the theory. There is no question that I turned the
surrogate key into a natural key. By making it a part of the data to
be modeled, I now have an additional attribute that fulfills all of
the characteristics of a key, by definition. Thus, a natural key has
been created. If you want to stick to definitions to imply a great
difference between surrogate and natural keys, then stick to them.

Autonumbers have been mischaracterized from the beginning of this
discussion. As you know, they are not "meaningless"; they are used as
a surrogate key which has a very definite purpose. As to being changed
arbitrarily, you are overemphasizing the fact that surrogate keys
aren't part of the data to be modeled. We get it. We just find the
distinction to be trivial. Once I assign the autonumber it seems to us
as if this assignment now becomes part of the data to be modeled. If I
start using the surrogate key in other systems (or giving it out as in
my example), this fact becomes even more obvious as suddenly you can't
just change the key without repercussions anymore.

You also have an inordinate amount of faith that people aren't using
the surrogate key except in the database. I don't know about you, but
I have no doubt that my database generated movie rental id, library
card id, etc. are not being shadowed by an internal surrogate key.
They have actually given me a card with the key on it, and it
definitely isn't formed by collapsing other attributes to form an
artificial key. It happens.

Again with the misstatement of theory. Surrogate keys are defined and
their use is provided for in the theory. Computer systems have nothing
to do with it. You seem to be looking for a lot of additional
significance to what is a definitional difference betweens keys that
are in the data to be modeled or not. It just isn't there.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Tuesday, June 15, 2004 8:27 AM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] OT: The Great Primary Debate
> 
> 
> Francis,
> 
>   Yes, you have a handle on it.  I don't agree 100% that the 
> autonumber
> would become a natural key in the sense that you describe (as 
> I mentioned
> earlier, there are a lot of gray areas in terms of what "is" 
> is<g>), but for
> the sake of argument, let's assume it does.
> 
>   Given that, I would doubt you could so casually discard it 
> once again as
> information would then exist that uses that number.  So now 
> it is no longer
> meaningless and cannot be changed arbitrarily.
> 
>   But Autonumbers in the typical sense are not used in that 
> way.  They are
> used as tags or pointers, or in other words surrogate keys.  
> They can be
> changed at anytime without affecting anything.  I say 
> "typical" in that we
> have no control over them.  I'm not saying that they can't be 
> used the way
> you describe, but one would normally want to retain control over an
> attribute.  For example, in what you outlined, I certainly 
> would not want
> gaps in a sequence, as I would derive information value from 
> the numbers
> assigned. So I would still stand by the statement that an 
> autonumber cannot
> be a natural key.
> 
> <<Fun, but hardly the significant difference you have made it out
> to be.>>
> 
>   There is a significant difference.  The point is that when 
> discussing keys
> one needs to be aware of the different view points that exist between
> relational theory and computer systems.  The term "key" means 
> very different
> things.
> 
> 
>   That will be it for me for a bit.  Work has really heated 
> up the past day
> or two.  Have a client in Phoenix that's been giving me fits. 
>  Makes for a
> long day with a 3 hour time difference<g>.
> 
> Jim
> jimdettman at earthlink.net
<snip>