Primary Keys - Autonumber or Unique Field Value (1 Viewer)

[Cosmos75]

Is it better to use an autonumber as a primary key OR to use a field in a table that will alway be unique? I read an article by Luke Chung (President of FMS), that the key field for a table should be meaningless and numeric and that he recommends using an Autonumber field as the key for "most" tables.

Should I always use an autonumber then?

 

[The_Doc_Man]

I concur with Pat, and in fact strongly believe that it is worth going a little bit out of your way to use meaningful primary keys WHEN IT IS REASONABLE TO DO SO.

Things that can reasonably be primary keys include

Employee ID number within a company.

Computer's serial number combined with brand-name.

Customer ID number used for mail-out autosort.

Don't go out of your way if you have to force it. In that case, use the autonumber.

 

[Cosmos75]

Here's the link to the article I mentioned.

Database Normalization Tips
by Luke Chung
President of FMS
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

 

[Cosmos75]

Natural vs Meaningful key

What is the difference between a "natural" key and a "meaningful" key?

Say for example I am keeping track of paint usage? The name of the paint is always unique so doesn't that make it a "natural" key? Would it also be a "meaningful" key? Or is it not a meaningful key because it only uniquely identifies the paint?

 

[David R]

Well...

The name of the paint may be unique, now, but what if you change the formulation? This goes back to our little organic chemistry discussion of a month or so ago; if you need to know what the formulation was in 1999 and you've upped the Toluene content since then, do you a) rename the paint, or b) make the PK include both the Name and the Year. At that point you're starting to get into 'stretched' meanings of meaningful...if you get my meaning.

A meaningful PK that would probably never change would be a SNN, or a Invoice#.

 

[Cosmos75]

Dazed and confused...

Just when I thought I was getting the hang of table stucture design...

 

[The_Doc_Man]

A natural key vs. a meaningful key, eh?

In this discussion, don't assume that either key has to be "prime" to be involved here.

"Natural" keys are keys that sort of "leap out" at you as "Of course that's a good key." For instance, the Library of Congress book number is a PERFECTLY natural key if you run a bookstore that only carries books registered in the Library of Congress. But it has no particular meaning unless you look up the book in your database.

The Dewey Decimal System for libraries also would be a natural providing you could keep the numbers consistent. In this case, the key would also have meaning because the numbers of the Dewey system convey categories. So the Dewey system would be both "natural" AND "meaningful" for a book key.

"Meaningful" keys are keys that when you look at them, you know immediately what they mean. One could imagine a key of astronomical information based on a body's formal designator. So we would recognize Rigel, Arcturus, Bootes, Betelgeuse, Sirius, Antares, etc. Astronomers would recognize Tau Ceti, Alpha Centauri, Beta Orionis, Gamma Draconis, etc.

Yeah, I know, long names aren't always good keys for efficiency reasons - but they would be good examples of MEANINGFUL keys.