[AccessD] Primary Key Best Practices

Yes, John, we know.  Now wipe the foam off your lips and lie down
quietly until the fit passes.  LOL

Charlotte 

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

I always and only use autonumber type PKs.  

I differentiate between PKs and unique indexes, whereas many people do
not.
A unique index's job is to prevent the same "unique" data from going
into the table twice.  A PK's job is to act as a pointer to a specific
record and to allow EFFICIENT joins between tables (act as a pointer).
Those are two completely different functions, and they BOTH must be
addressed, but they do NOT have to be addressed in the same structure.
In fact the EFFICIENT join PREVENTS them being addressed in the same
structure!

Pulling fields from parent / grandparent /
greatgrandparent/greatgreatgreatgreatgreat...grandparent tables down
into the current table just causes a slew of problems, from performance
to update issues. Autoincrement PKs neatly sidestep all of those
problems.  

In my experience, most of those who demand natural keys use
"convenience"
reasons such as "I can always look in any table and see where the data
comes from or who owns the data (lineage).  True, but irrelevant.  That
is laziness, not requirement.  I have also heard a lot of arguments like
"if parent records are deleted I can still see who the data belongs to".
True but irrelevant.  That is again laziness (too lazy / sloppy to
implement RI).
I do not allow parents to be deleted unless children are deleted.  I
have been doing this a long time and NEVER REQUIRED a natural key, and
in fact AFAICR have never built a table with a natural key.  

Now I will grant there is a gray area such as "colors" where the color
is the only "value" field in the table and so "why not" use it as the
PK.  The answer comes back to speed issues.  Integers are faster to deal
with inside of a computer when doing things like joins.  Thus even in
such cases I use autonumber PKs.

I can tell you that I have been doing databases since 1995 and have
never, in even one case, REQUIRED a natural key.  I DO require unique
indexes just as anyone else will.

Just my opinion, ymmv etc.

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 Jim Hewson
Sent: Tuesday, July 24, 2007 10:55 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Primary Key Best Practices

I need a few good references (preferably electronic) on the Best
Practices for defining Primary Keys (PK) Here at the office we have had
a few heated discussions about primary keys.
I don't' mean to stir the pot on this list, just curious what others
think.

1.  All tables should have a primary key even though it will not be used
as a foreign key (FK) in another table.
2.  PKs should always be an autonumber (Access), Identity (SQL) or GUID.
3.  Avoid complex PKs such as two PKs from two other tables.
4.  Avoid complex PKs such as two fields from one table.

You could probably come up with some more.

Thanks,

Jim H. 
 

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

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