[AccessD] Autonumber Assigned Immediately

PKs are nothing more than pointers to child records.  If a table has no
child objects, then it has no need for a PK.  If you break out the multiple
uses that developers have traditionally give the PK into its component
pieces, you have the pointer function, and the unique index function.  They
are entirely different functions, with entire different requirements.  One
does not replace the other, the pointer function is not always required, the
unique index is not always required.  If you break the pieces apart and deal
with them separately then you have the ability to deal with them as required
by that piece.  

I ALWAYS add an autonumber PK ID to every table, just as a matter of course.
Understand that the PK is nothing more than a pointer to child records in
related tables.  I don't always need one, but I just put it there.  This is
a long int, automatically handled by the database engine and has little
(almost no) overhead, so I "just do it".

I ALWAYS analyze whether I need a unique index on a table.  When I need to
prevent adding duplicate data, I add a unique index.  MOST of my tables have
a unique index, but not every single one.  

The fact that I use an autonumber PK does not in any way relieve me of the
responsibility of analyzing whether I need a unique index, and if so which
field or fields will make up the unique index.  

PKs and unique indexes are different constructs and serve different
purposes.  Neither of them are always required.  If you break these two
things apart and treat them as the separate entities that they truly are,
then it becomes possible to use each for the exact purpose that they exist,
and makes it almost trivial to determine when and where each is needed.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Friday, September 09, 2005 9:47 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Autonumber Assigned Immediately


If this mysterious data storage bug trashes autonumbers, how does it
magically leave the data alone so you can still construct your natural keys?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, September 09, 2005 8:22 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber Assigned Immediately


John,

 <<I have used autonumbers for EVERY SINGLE PK in EVERY
SINGLE database I have ever designed (and that is a LOT of databases) and
have NEVER, even ONCE had to modify my PK, chase down FKs, add new fields to
my PK/FK etc.>>

  True, but if there is ever a bug in your storage system and your keys get
scrambled, then your really up a creek.  A natural PK design would not be as
the key is always constructed from the data.

  Granted, something that fundamental would (one hopes) get caught pretty
quick, and there are always backups.

Jim.