[AccessD] 2 quick questions

Jim Dettman jimdettman at verizon.net
Tue May 31 15:39:51 CDT 2011


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.



  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Monday, May 30, 2011 09:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] 2 quick questions


Jim,
I don't want to reiterate this discussion but your comment puzzles me.
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.
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 autonumber field? And also: when implementing a foreign
key, do you make this key point to the natural key rather than to the
autonumber key?
If your answer is yes to any of these questions, then I disagree and we have
a point of discussion.
If not, I don't get the meaning in your comment. Then you are in accordance
with Robert, and I suggest with most of us.
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. 
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman
Sendt: 30. maj 2011 02:19
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] 2 quick questions

<<Primary Key = Nothing a user can use for anything and is totally not 
dependant on data. For db system use only>>

  Pure hogwash.  The very definition of a PK in the relational model is
exactly opposite of what you describe.

  John's problem is exactly what I tried to point out on this list a while
back; there is a fundamental difference between a primary key and a tag or a
pointer.  John's problem is related to the meaning of the data itself; how
to determine that a row within the relation is unique.  In other words, John
is looking for the primary key; that combination of attributes which
identifies a specific row and ensures that the row is not duplicated.  

  That is not the same thing as adding a unique tag or pointer on every row
(an auto number).  And despite popular belief, a auto number in relational
terms is not a surrogate key.

  John needs to come up with a key based on the data that will ensure that a
row is unique.  If the field(s) used to do that cannot ensure that, then
more fields (attributes) need to be added to the table (relation).

  For *performance* reasons in the DB, he will use an auto number as a
pointer (like all of us do), but in addition to that, he will need to create
a unique index based on the PK.

  He might take the shortcut of making that index a hash, which for
performance reasons may be required, but it's a bad idea because it can lead
to an update abnormalities.  If you can live with that, great and at the end
of the day, it may be required to get the job done.

  Since it's only one index on the main table, I wouldn't take that
shortcut.

  And as far as the primary key not being able to change (as in a name
change or SS#), again total hogwash.  That's why relational DB's allow for
cascading updates.  There is nothing stated in any paper on the relational
model that says a primary key cannot change.

Jim.
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert Stewart
Sent: Friday, May 27, 2011 04:03 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] 2 quick questions

Primary Key = Nothing a user can use for anything and is totally not 
dependant on data. For db system use only

Business Key = Something within the data that is unique and can be 
used by a user for identification.

Business Keys can be one or more fields, when more than one, it is a 
Composite Business Key.

Remember the above and you will not have any issues.

Example:

tlkp_NameSuffix
NameSuffixID   Autonumber
NameSuffix      Text(30)

NameSuffixID is the primary key
NameSuffix is the business key, and has a unique index on it.


Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com

At 01:18 PM 5/27/2011, you wrote:
>Date: Fri, 27 May 2011 08:18:47 -0500
>From: "Dan Waters" <df.waters at comcast.net>
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] [Spam]8.31 Re: 2 quick questions
>Message-ID: <001101cc1c70$9d48a380$d7d9ea80$@comcast.net>
>Content-Type: text/plain;       charset="us-ascii"
>
>A field with an 'Intelligent Key' is fine - as long as you don't use it in
a
>table relationship.  In other words, always have an arbitrary (autonumber,
>etc.) key as the key that is used in table relationships.  That way you'll
>NEVER have to spend hours or days rebuilding tables when the key you used
in
>a dozen or more table relationships is changed.
>
>Dan
>
>PS - Even RFID's can be swapped out, and Tattoos can be removed!
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
>Sent: Friday, May 27, 2011 1:06 AM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] [Spam]8.31 Re: 2 quick questions
>
>Intelligent Keys are an excellent concept when you can attach them  to  the
>item concerned or to it's container.
>
>Unless you can tattoo them or implant an RFID chip, there is little point
in
>trying to use IKs to identify people.  :-)
>
>--
>Stuart
>
>On 27 May 2011 at 1:55, Arthur Fuller wrote:
>
> > I don't know where you obtained this rule against the use of
> > Intelligent Keys, but I seriously object, and cite Chris Date and
> > Fabian Pascal and the inestimable Joe Celko for reasons why IKs are a
> > good thing, in certain applications (eggs are a bad example; car parts
> > are an excellent example).
> >
> > A.
> > --
> > 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


-- 
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