Choosing a primary key

  • Thread starter Thread starter Top Spin
  • Start date Start date
T

Top Spin

Is it considered good db design to ALWAYS have an autonum field as the
primary key -- say, as opposed to some unique data field, like part
number or account number?

If so, what are the reasons?

Thanks

--
Running MS Office 2K Pro
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
Email: Usenet-20031220 at spamex.com
(11/03/04)
 
There's no reason that you HAVE to use an autonumber; just the fact that you
have a primary key is a good thing. The good part about using an autonumber
is that it's quick and easy, and it will not ever repeat, so lends itself
well as a primary key to uniquely identify a record.

A bad thing would be to rely on the autonumber as a meaningful piece of
information for your record; like using it as an invoice number or account
number. Autonumbers can get be something around +/- 2 billion or so.
 
When I have "natural" keys, I tend to use them rather than creating
"artificial" keys. If you do choose to use an autonumber pk when you have a
natural key, you should add a unique index for the natural key to prevent
duplicates.
 
There's no reason that you HAVE to use an autonumber; just the fact that you
have a primary key is a good thing. The good part about using an autonumber
is that it's quick and easy, and it will not ever repeat, so lends itself
well as a primary key to uniquely identify a record.

A bad thing would be to rely on the autonumber as a meaningful piece of
information for your record; like using it as an invoice number or account
number. Autonumbers can get be something around +/- 2 billion or so.
Click to expand...

I was just mainly asking if "good db design principles" include always
using a separate (from the user data) autonum field and, if so, why.

The advantages that I can see are that it is (a) guaranteed to be
unique, (b) quick and easy to set up, (c) independent of the data (so
that teh rest of the data can be changed without affecting the links,
and (d) readily understood by everyone.

The only disadvantage that I can think of is that it is an extra field
(and extra space).

Thanks


--
Running MS Office 2K Pro
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
Email: Usenet-20031220 at spamex.com
(11/03/04)
 
When I have "natural" keys, I tend to use them rather than creating
"artificial" keys. If you do choose to use an autonumber pk when you have a
natural key, you should add a unique index for the natural key to prevent
duplicates.
Click to expand...

Good point.

If I do have a natural key field and I choose to make it the pk and
then later discover that it isn't unique after all (maybe because the
data specs changed), it is a huge problem to convert that table to an
autonum pk?


--
Running MS Office 2K Pro
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
Email: Usenet-20031220 at spamex.com
(11/03/04)
 
A natural key always opens the database to the possibility of a typo
mistake. An autonumber never does.
 
A natural key always opens the database to the possibility of a typo
mistake. An autonumber never does.
Click to expand...

Sure it does. It's WORSE with an autonumber, because it lets you put
in the erroneous record even if it duplicates some other record.


John W. Vinson[MVP]
 
And what stops you when using a social security number, mistyping it by one
digit and entering a duplicate of a previous record?

Steve
PC Datasheet
 
PC said:
And what stops you when using a social security number, mistyping it
by one digit and entering a duplicate of a previous record?

Steve
PC Datasheet
Click to expand...

The point is that there are TWO different objectives. The database engine
needs a way to identify rows for updates and relations and there is a HUMAN
desire/need to have correct data with no duplicates. An AutoNumber
satisfies the former, but does nothing about the latter. A natural key can
do both, but can be a PITA if it takes more than one or two columns to
establish uniqueness or if it is not guaranteed to be static.

Database admins and developers do many things because they make their jobs
simpler. This is not the same thing as doing them because they adhere to
proper database theory.
 
If you have even the remotest suspicion that the natural key may not be
unique, DO NOT use it as the pk. Go with the autonumber and unique index.
It is easy enough to remove the unique index if it turns out that the natural
key is not really unique. It is a royal PITA to to swap pk fields after the
db is built. The more related tables you have the worse it is.
 
I tend to favor using a autonumber. And, even when I have something like a
invoice number, I still do NOT use that as the link for my relationships.
The reasons for this are many, but for example, you might want to setup a
customer invoice, but NOT generate the invoice until a certain approval, or
other things have taken place. By using a autonumber, the applications will
continue to run.

the same goes for a social insurance number etc. My payroll system would
allow you to enter names, and continue to function while the user brings in
their social insurance number. In other words...why should my application
break, or not function just because I don't have (or even want) a invoice
number until the invoice is complete?

Further, users don't care about how relationships work....I mean, do you
care about the memory references used when word functions? Stuff like
relationships, and memory pointers and software code is for the
developers..and the end users don't have to (or should not have to) even
care, or even be aware of such things. There is zillions of memory, and
pointers and all kinds of things that allows your computer to run word, but
the users never see the code, or memory values that the developers used to
make word function.

When you use a commercial application like QuickBooks, or even use outlook
for contact management, do you even care, or think about the ZILLIONS of
fields and values used that you DO NOT SEE to maintain the relationships?
(no...you don't care one bit).

Good software just functions...and as a general rule stuff used for
relationships should just work..the same goes when you get up and turn on
the lights..they just work..and you don't start thinking about electrical
wiring that makes the light bulb go....
 
I tend to favor using a autonumber. And, even when I have something like a
invoice number, I still do NOT use that as the link for my relationships.
The reasons for this are many, but for example, you might want to setup a
customer invoice, but NOT generate the invoice until a certain approval, or
other things have taken place. By using a autonumber, the applications will
continue to run.

the same goes for a social insurance number etc. My payroll system would
allow you to enter names, and continue to function while the user brings in
their social insurance number. In other words...why should my application
break, or not function just because I don't have (or even want) a invoice
number until the invoice is complete?
Click to expand...

That's an excellent point. Thanks

--
Running MS Office 2K Pro
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
Email: Usenet-20031220 at spamex.com
(11/03/04)
 
Back
Top