[AccessD] Autonumber Assigned Immediately

It is a natural consequence of life.

There are always going to be natural or business keys that are composites.
For example, your full name is not unique.  But combine the first, last,
and middle name with your SSN and the odds are very good that the
combination would be a unique natural/business key.

But, I would never be stupid enough to use that combination as a
primary key for any reason.  Surrogate keys, autonumber or Base36,
are quite adequate for me.  Base36 is really cool if you want to
drive the autonumber, everything has to be sequential, Nazi's
crazy.  The same ID never occurs in the entire database twice.

Characters
in PK       Possible unique records
  6               2,176,782,336
  7              78,364,164,096
  8           2,821,109,907,456
  9         101,559,956,668,400
10       3,656,158,440,062,000

Hands down, surrogate keys are the best design technique.  Then
enforcing natural keys, candidate keys, and business keys through
indexing or constraints to keep your data clean.

Performance is always going to be poor when you have to join with
multiple columns across tables.  It does not matter what DB you use.

Robert

At 08:27 AM 9/9/2005, you wrote:
>Date: Fri, 9 Sep 2005 08:23:38 -0400
>From: "Jim Dettman" <jimdettman at earthlink.net>
>Subject: Re: [AccessD] Autonumber Assigned Immediately
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID: <NDEALJOEGMMPCDKIFGNFCEBOFDAA.jimdettman at earthlink.net>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>David,
>
><<Natural keys, 3 to 5 PKs per table. table joins
>require three to five joins. THis would have been
>simplified with one autonumber PK.>>
>
>  But is that a fault of the design or the fault of the DBS/Computer system
>(I'm assuming your implying that performance was poor).
>
>Jim.