[AccessD] Primary Key Best Practices

Hmmn ... Logical, reasonalbe ... Foaming mouth... I wonder?? LOL

Charlotte 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, July 25, 2007 8:49 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com