[AccessD] Primary Key Best Practices

Jurgen,

Why can't I state it in such reasoned and logical terms?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----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