table keys: single-field or multiple-field?

[annie]

My formal database education (a graduate school course in an Information
Systems program) armed me with the wisdom to put a lot of effort into table
design and normalization before implementing the database, and I follow this
approach religiously. Choosing proper key fields is an obvious part of
this. As a result, I often have tables with multi-field keys.

However, in more than one instance over the Internet I have read from others
who claim that using a meaningless, single-field key for all tables is the
best way to go. This strikes me as being counter to the design theory that
I learned.

I suspect that there are pros and cons to both approaches.

I'd like to hear what others think about this.

Thanks!!!

 

[John Vinson]

However, in more than one instance over the Internet I have read from others
who claim that using a meaningless, single-field key for all tables is the
best way to go. This strikes me as being counter to the design theory that
I learned.

I suspect that there are pros and cons to both approaches.

That's putting it mildly <g>. This is a subject upon which many people
have strong and differing opinions, and this question can lead to
quasi-religious wars on the newsgroups!

In my *own personal opinion* - recognizing that others have different
opinions - if you have a good natural key you should use it. A *good*
key is one that is unique, stable, and (less critically) simple/short.
People's names fail on all three counts, for instance, so I'd never
use a name (or even a name and address, or name and phone number) as a
key - I know two men named Fred Brown who at one time shared the same
address and phone.

If your candidate key does not share these drawbacks, by all means
consider it. In practice carrying a (say) five-field key down through
four or five layers of related tables gets awkward, in terms of disk
and memory use, performance, and comprehensibility; in such cases one
can (and I very often will) use a unique Index on the fields to ensure
uniqueness, but use an Autonumber as a surrogate key, just so the
related tables are simpler.

Tom Ellison, whose opinions I respect highly, leans the other way - he
strongly prefers the use of natural keys whenever at all possible, and
makes a convincing case that doing so is more beneficial than harmful.
Try googling for his name and the phrase "primary key" to see some of
his comments (if indeed he doesn't join this discussion).

I'd be interested to see what some of the other experts opine!

 

[Steve Schapel]

Annie,

I never use multiple-field primary keys. If a table could ever be on
the "one" side of a relationship, and if there is not a single field
which can serve as the primary key, I will always whack in an
autonumber field. My reason: in a choice between simple and
difficult/complicated, I'll go for simple every time.

I do sometimes use multi-field unique indexes where applicable... but
that is a different topic.

Oh, and as John mentioned, respect for the opinions of others on this
topic is assured

- Steve Schapel, Microsoft Access MVP

 

[annie]

The last thing I want to start is a database jihad!!!

I certainly respect the opinions of others, and am open to hearing all
sides. I am very excited that two have been brave enough to come forward,
and I hope more do!

I don't have a problem departing from purist approaches when there are
benefits to doing so. While I am aware of the unique indexing capability on
fields, it never occurred to me to use that to retain some of the benefits
of designating a field as key. But how do we know that unique indexes
result in decreased memory use and/or increased performance? You don't get
something for nothing.

And how do you maintain referential integrity when your natural key fields
are not key?

 

[John Vinson]

The last thing I want to start is a database jihad!!!

I certainly respect the opinions of others, and am open to hearing all
sides. I am very excited that two have been brave enough to come forward,
and I hope more do!

I don't have a problem departing from purist approaches when there are
benefits to doing so. While I am aware of the unique indexing capability on
fields, it never occurred to me to use that to retain some of the benefits
of designating a field as key. But how do we know that unique indexes
result in decreased memory use and/or increased performance? You don't get
something for nothing.

Indexes (unique or otherwise) can almost always be counted on to
improve *retrieval* performance; this is always at a cost (usually
managable) in *update* performance. The JET query optimizer is IME
very good at taking advantage of existing indexes to speed searching
and sorting; of course, when you add a record or change the value of
an indexed field, there's some cost to rebuilding the index, but with
most databases this isn't any big deal.

And how do you maintain referential integrity when your natural key fields
are not key?

By maintaining referential integrity between the surrogate Primary Key
and the Long Integer foreign key, and not putting any of the natural
key fields in the related tables - only in the "one" side table - and
using queries to link to them as needed.

 

[annie]

I'm only a few years into this stuff, BUT ... perhaps these methods of
getting around performance problems might amount to bad and ill-advised
habits when processor speed inevitably advances suffiicently. In my mind,
it would be a mistake to develop habits that are dependent on current
technology.


GO PATRIOTS!!!

 

[Steve Schapel]

Annie,

I don't have a problem departing from purist approaches when there are
benefits to doing so.

I think one thing to note is that "purist" is often in the eye of the
beholder! I mean, "purist" according to which system?

- Steve Schapel, Microsoft Access MVP

 

[annie]

I meant the natural key system, i.e., not the all autonumber key system.