[AccessD] 2 quick questions

Arthur Fuller fuller.artful at gmail.com
Tue May 31 20:15:27 CDT 2011


I refuse to participate in this conversation. Apparently, few if any of you
(I reserve one exception) have never read Codd or Date etc. Yes, it is
convenient to use an AutoNumber (or in SQL parlance Identity, or in Oracle
parlance Sequence) to uniquely identify rows within a relation. Of course it
is, and that's why most of us use it, but is it correct? Actually, I think
not, atlthough sometimes it shall suffice: given the case of thousands of
eggs hatched by hundreds of chickens daily, it may not make sense to give
them Intelligent Keys, but given another case such as serial-numbered
automobile parts, then non-autonumbered PKs make serious sense.

I am not on one side or the other of this discussion. Rather, I am on both
sides, and can see the sense in both sides of this discussion. When we are
discussing eggs, autonumber may seem correct; when discussing fuel
injectors, then serial numbers and batch numbers are important, and hence
PKs should identify these objects intelligently, not autonumerically.

A.

On Tue, May 31, 2011 at 6:14 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> Just trying to get you to a shrink!  We'd all be happier.
>
>
> ;)
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 5/31/2011 5:39 PM, Jim Dettman wrote:
>
>> <<We (or I) understand from whence your angst comes but we (or I) feel no
>> need to participate in the
>> angst.  And no matter how you try, you will not force me to participate in
>> your angst.>>
>>
>>   Hum, well seems kind of funny that you always feel the need to respond.
>>
>> Jim.
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Tuesday, May 31, 2011 05:08 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] 2 quick questions
>>
>> That is the most lucid explanation you have ever attempted, and suddenly I
>> understand your angst
>> (though I do not participate in your angst).
>>
>> Unfortunately for you, pretty much nobody cares.  I for one long ago split
>> the PK concept into:
>>
>> 1) Pointer
>> 2) Unique index on a selected key.
>>
>> Having done that the PK is the pointer and the unique index is... a unique
>> index.  Which of course
>> is the beauty of doing this.  The surrogate never changes (values or
>> fields)
>> and the unique index
>> can change with the flick of a key.  All the messiness of a real world PK
>> goes away.  I embrace that
>> with all my heart.  I love it.  Semantics aside, it just works!
>>
>> We all understand perfectly well that in the relational model the PK is
>> both
>> and cannot be split
>> into these two pieces but I am forced to work in the real world where the
>> PK
>> is (at least when
>> surrogates are involved) *only* the pointer part of the equation.  *And*
>> more importantly, everybody
>> that I am working with and discussing things with (with the *sole*
>> exception
>> of you) uses word PK in
>> the same manner as I do.
>>
>> Thus we pretty much ignore your protestations and keep on doing what we
>> do.
>>
>> Nobody is claiming that selecting a key to use for ensuring uniqueness,
>> nor
>> creating the unique
>> index is not important, it is simply that I (we I dare say) view it as
>> distinct from and not
>> necessarily related to the pointer between records.
>>
>>  >But then all of a sudden, we come to the PK and slap in an auto number
>> and
>> it's the "PK".  But
>> it's not because you've shifted from a logical context to a physical one.
>>
>> Well, the physical world forces us to do so.  Why is this a problem?  We
>> must select the pointer,
>> and the logical PK usually sucks as the pointer so there you are.  We were
>> told by the world to call
>> the pointer the PK and so we do.  Only you have angst over it.
>>
>> What is Lead?
>>
>> 1) A metal
>> 2) A piece of metal attached to an electronic component.
>> 3) The position out in front.
>>
>> We often call distinct and not necessarily related things buy the same
>> word.
>> We are doing that
>> here.  Unfortunately (for you) in this case we are calling both parts (the
>> pointer and the selected
>> key) the same name and you go ballistic.
>>
>> We (or I) understand from whence your angst comes but we (or I) feel no
>> need
>> to participate in the
>> angst.  And no matter how you try, you will not force me to participate in
>> your angst.
>>
>> Go see a shrink and see if you can get over it.  ;)
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>> On 5/31/2011 4:39 PM, Jim Dettman wrote:
>>
>>> Asgar,
>>>
>>> <<I quite agree that an autonumber is not a primary key, but I would then
>>> hasten to specify that it is not so in the *logical design* - in the
>>> *physical implementation* it certainly could (and IMO should) be.>>
>>>
>>>    Yes but when modeling data relationally the meaning of what a "Primary
>>> Key" is only occurs in the logical sense.  Relational modeling only deals
>>> with how data is associated logically and cares nothing about how it is
>>> physically stored.
>>>
>>>    That's why I typically say "Your mixing apples and oranges" when one
>>> states that an auto number is a "PK", because it's labeled as such in
>>> database products.  To call it a "PK" is misleading; we all supposedly
>>> design our databases using the relational model (logically); each table
>>> represents one thing, we follow the rules of normalization, etc.  But
>>> then
>>> all of a sudden, we come to the PK and slap in an auto number and it's
>>> the
>>> "PK".  But it's not because you've shifted from a logical context to a
>>> physical one.  That's where the apples and oranges come in.
>>>
>>>    John's problem is proof of that.  Even though he has an auto number
>>>
>> "PK",
>>
>>> he's still trying to figure out how to prevent duplicates in his table.
>>>
>>> <<So just to get you right I would like to know:
>>> Do you actually implement the natural key as a primary key and then
>>> create
>>>
>> a
>>
>>> unique index on the auto number field? And also: when implementing a
>>>
>> foreign
>>
>>> key, do you make this key point to the natural key rather than to the
>>> auto
>>> number key?>>
>>>
>>>    Typically you do the opposite; an auto number for the "PK", and then a
>>> unique index based for the PK. Or you assign an ID as a surrogate.  It
>>>
>> might
>>
>>> even be an "auto number", but to me, that's no longer an "auto number"
>>>
>> even
>>
>>> though the field type says it is. You might also go the route of checking
>>> the PK in code by doing your duplicate check based on user input.
>>>
>>>    But when all is said and done, in some way, you need to use the PK.
>>>  If
>>> you don't, then you don't have a valid relational model for your data.
>>>
>>> <<The disagreement appears to me to be a matter of wording, or if you
>>> prefer: definition - neither of which are trivial! And distinguishing a
>>> *logical PK* and a *physical PK* would clarify the discussion.>>
>>>
>>>     In a large way it is, but again to me, there is no such thing a PK in
>>>
>> a
>>
>>> physical context.  Being a PK is a lot more then simply labeling a row
>>> uniquely physically and that always seems to be the bone of contention.
>>>
>>> Jim.
>>>
>> --
> 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