[AccessD] Primary Key Best Practices

Charlotte Foust cfoust at infostatsystems.com
Wed Jul 25 11:41:06 CDT 2007


It's usually the approach I use for M to M tables, and the only drawback
I can think of is that if you needed a foreign key back to this table,
you'd have to use both/all PK fields, not just a single autonumber.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow
Sent: Wednesday, July 25, 2007 8:31 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

Jim,
This is something I haven't seen before and is in my early morning mind,
quite brilliant! Are there any drawbacks to doing this?

I can't imagine it would be much of a difference (size or performance
wise) in any of my applications / databases but it is certainly
something worth considering implementing in the future.

Always open to technical improvements...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman

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.








































































































































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