[AccessD] Primary Key Best Practices

Jurgen,

<<The question was about best practices, not relational theory.>>

  Excellent point. 

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Wednesday, July 25, 2007 11:39 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Primary Key Best Practices

Jim:  I'm generally with John on this one.

The question was about best practices, not relational theory.  Every comment

you made to John was about relational theory.  This is about computer 
databases; about 'Practices', not 'Theory'.

In practice, you can not beat an autonumber for size and speed (performance)

in a computer environment.  Apply it consistently in conjuction with any 
other unique indexes on fields or combinations of fields you identify as 
candidate keys and no user can mess up any more than they possibly could 
using any other key.  None of the 'Theory' comments impact the practical 
utility and validity of using a unique long or GUID as a primary key.

If occasionally you have a key that will likely not be used in a join, at 
least you retain the flexibilty should the unforseen arise.  In terms of 
additional space wasted for the case where such a key is never used in a 
join, it is the smallest amout possible for the flexiblity and consistency 
gained.  That sounds like a best practice to me.

Regardless of whether anyone agrees with me, it is my practice because it 
works well.

You won't hear from mea again on this topic this year.


Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Jim Dettman" <jimdettman at verizon.net>
>
>John,
>
><<1) The PK should be stable (NEVER changes).  >>
>
>   Don' agree with "never".  You want it as stable as possible, but there 
>is
>nothing in relational theory that says it can never change.
>
><<Again, the PK and the unique index are NOT the same structure, either in
>real life or in theory.>>
>
>   That is simply not true.  With relational theory, the PK is derived from
>the pool of CK's, which is based on the data.  In theory you cannot have a
>PK without it being a unique index.  That's part of the definition of what 
>a
>natural PK is.  Out of the pool of CK's, you would use the CK that is the
>most stable, shortest in length, and the least complex (simple vs 
>compound).
>
><<What works, and what is the best choice are different matters.  The
>surrogate key avoids a whole slew of real life problems, and creates none
>(in my experience).>>
>
>   Surrogates only work in computer systems because we have systems that 
>are
>reliable.  If I gave you pen and paper and had you keep track of data using
>the relational model, you'd be in a pretty big mess fairly quick if you 
>used
>surrogate keys.
>
>   I don't object to the use of surrogate keys, but what I do object to is
>the apples and oranges approach you use to claim what a PK is and surrogate
>keys fit in the relational model.  Surrogate keys are nothing more then
>pointers.  To call them a PK in regards to the relational model is simply
>incorrect.
>
>   Surrogate keys work well in computer systems, but the relational model 
>can
>be applied to much more then computer systems and the PK as you describe it
>vs what it is in the relational model are not the same thing.
>
>Jim.

_________________________________________________________________
Fight Allergies With Live Search 
http://search.live.com/results.aspx?q=Remedies+For+Spring+Allergies&mkt=en-c
a&FORM=SERNEP