Re: do all primary keys use autonumber

Tech-Archive recommends: Fix windows errors by optimizing your registry

I agree with most of this, but suggest that one should be a bit cautious, depending on individual circumstances.

John Vinson wrote:

On Thu, 10 Nov 2005 02:33:04 -0800, "k" <k@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

No, certainly not.

An Autonumber is convenient if you don't have a "natural" key, but it
can actually be a bad idea. It makes all records unique automatically,
so that if you have duplicate data, it can be harder to discover.

This is a good point, but if it's important to be sure that the data are unique, that can also be accomplished via an index that is separate from the primary key.

If you have some field - or combination of up to ten fields - which
uniquely identifies each record in your table, use it.

[...]

Depending on how the database is structured, this might or might not be a good idea. I normally try to avoid using a multiple-field primary key, merely because I think it would be a nuisance to maintain all those fields (as parts of a foreign key) in the other Tables that refer to the current Table. A foreign key that is merely a long integer seems a lot tidier. Conceptually, there's no difference, but the long integer occupies less space and (I expect) takes less time to compare when sorting or searching. At the least, I'd want all or many of those extra fields to have some value in the other Tables other than just being part of a foreign key.

OTOH, I might want to store those long multiple-field key values in the other Tables if I were worried about the time required to accomplish an indirection. Microsoft suggests avoiding linkages requiring more than about half a dozen indirections -- this is kind of a rule of thumb, I suppose, but it seems like good advice. So if you can use foreign-key values directly instead of looking them up, and if you don't have to update them very often, they might be part of a good design in your case.

My own preference is to lay on the indirect linkages (usually via Autonumbers) pretty heavily early in the design stages, when I want to be sure that redundant data are kept to a minimum. Then in the later stages, when I might be more concerned with performance, I might duplicate some of the key values to reduce indirections. Of course, if I'm /really/ concerned about performance, I might go to SQL Server, or ISAM on flat files, or some other system besides Access. But Access has the advantage of being relatively easy to use and inexpensive, and performance on modern computers usually does not have the same importance it did in the olden days.

  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.
.

Relevant Pages