[AccessD] Primary Key Best Practices

Susan,

  True, but in the case of a M to M table your just introducing another
column and a index that really is not needed.  It seems silly to me to have
something like:

tblLinking
LinkID - Autonumber - PK
BookID - Long - CK1A
AuthorID - Long - CK1B 

Instead of

tblLinking
BookID - Long - PK1A
AuthorID - Long - PK1B


  just for the sake of having an auto number in there.  The BookID and
AuthorID already form a unique pair and they typically would not be used as
a foreign key in another table.

Jim.  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, July 24, 2007 2:48 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

 

  I don't agree with that 100%.  Surrogate keys are in widespread use and
for most tables are used 98% of the time in today's designs, but I think
it's really silly to add a surrogate key to a many to many linking table
when you already have a FK pair that must be unique and would serve
perfectly well as a PK.

======Other than habit -- consistency can be a good thing. 

Now to add to your list -- and you can thank Martin Reid for this additional
few: 

PK should be stable. In real life, things do change, hence cascading
updates, but use a key that's not prone to change.  
When using natural keys, choose the key with the fewest fields necessary --
I know that should be a given, but I've seen some that made me shake my
head... :( 

Susan H. 

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