[AccessD] 2 quick questions

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.