Primary Keys (to autonumber or not ?)

[TonyB]

I am trying to do something fairly simple in a access db. I have a table
that contains a record for every sale made. Each sale needs to have an
unique number of the form yyyy-mm-dd-xx (where xx starts from 01 each day)
allocated when a new sale is entered, along with other info in other fields.
This is the number used to lookup any particular sale.
So I could create a PK for the record which uses this field directly as the
key, which means not using an autonumber field.
Or I could use a combined key using an autonumber field plus a date field to
lookup records when required in a report or query, and convert the
auotnumber field plus date to the yyyy-mm-dd-xx form for reports, queries
etc.
I do want to search for records that have a particular value for
yyyy-mm-dd-xx, or all records for a particular day etc so I felt it makes
sense to create this field directly and use it as a natural key for the
table. But it also could be done using an autonumber field and date fields.
However when "googling" this topic there do seem to be strong contradictory
opinions as to whether you should use autonumber fields always in Access, or
always use a natural key if possible. What are the pros and cons in this
situation ?

Regards
Tony

 

[Lynn Trapp]

Tony,
I sit in the middle on this question. I believe there is a valid place for
artificial keys and an always essential place for natural keys. A natural
key is always the best to use, when possible. I say "when possible" because
some relations (tables) make it very difficult, if not impossible, to find a
natural key that can be guaranteed to be unique. Any table that stores
information about people is a good example. Names are nearly always going to
be duplicated in a large group of people and, as could be easily
demonstrated, practically every other field in a person table can be too.
You can easily have 2 John Browns living in the same house and sharing
virtually every other characteristic so that you might end up having to use
every field in the table as a candidate key. This would obviously be
unwieldly to develop and maintain. This kind of table almost cries out for
an artificial key -- although I know people who would argue with that, and
you may be hearing from them.

Also, an artificial key provides a much easier way to provide a link between
related tables. It's real benefit is for the database engine, not for the
user. That's why you will hear virtually everyone on here say that end users
should never be shown the value of an AutoNumber field.

At the same time, even those tables like I described above encounter a
problem if all you do for "data integrity" is to use an AutoNumber field as
the primary key. You are going to end up with duplicate records (those 2
John Browns I described above) and/or what I call "non-duplicate
duplicates." A "non-duplicate duplicate" is a duplicate record that has
differences from it's twin, but those differences are only the result of
data entry errors. I used to maintain a database that had records for A. T.
& T in its vendor file -- and it could have easily had more. You can handle
the problem of real duplicates by placing a unique index on a set number of
fields in the table or by running a routine that checks for duplicates
before the data is actually stored in the database and giving the user the
choice of saving it or not (I prefer this last one). The "non-duplicate
duplicate" problem may not have a solution because there are always going to
be spelling errors.

I hope this helps some.

 

[Jeff Boyce]

Tony

I agree with everything Lynn mentioned.

I fall into the "tends to use Autonumber" camp (but that is only a
tendency). In dbs with multiple related tables, I've found it more
efficient (read "lazy") to use autonumber primary keys to save effort in
relating the tables. This lets me get on to more interesting issues, like
user interface, without spending extra time/effort discovering candidate
keys and selecting the one I'll use. And Lynn's discussion of "duplicates"
is right on point!

But I also use a "natural" primary key when it serves no purpose to include
an artificial (autonumber) key.

Best of luck!

Jeff Boyce
<Access MVP>

 

[Lynn Trapp]

I agree with everything Lynn mentioned.

In dbs with multiple related tables, I've found it more
efficient (read "lazy") to use autonumber primary keys to save effort in
relating the tables.

I'm not so sure it's a matter of "laziness" as it is more effecient for the
database engine. Of course, there are people, who will remain unnamed until
they name themselves, that will disagree with that.

But I also use a "natural" primary key when it serves no purpose to
include
an artificial (autonumber) key.

That should be standard practice, in my view. A table storing department
information for a company has a perfectly good natural key of Department
Name, which should never be duplicate. Using an AutoNumber here does nothing
to speed up the processing, especially since the Department Name field would
have a primary index on it.

 

[Tim Ferguson]

I'm not so sure it's a matter of "laziness" as it is more effecient
for the database engine. Of course, there are people, who will remain
unnamed until they name themselves, that will disagree with that.

Me sir, please sir! I'm dead lazy and that's why I prefer natural keys...


<g>

Tim F

 

[Lynn Trapp]

<g> I knew someone would speak up...