Autonumbering Primary Keys

[CC]

Hi! I'm doing an orders database. For the primary key, I was going to use the
order numbers since those will always be unique. However, it seems that
Access suggests that I have a separate column for OrderID using Autonumbers.

Is there a reason I should always have a separate ID column as the primary
key? Should I not use Order numbers or part numbers as primary keys?

 

[Mark]

As long as your order number or part number will always be unique (or should
be), go ahead and use it as the primary key. You can even select multiple
fields and make the combination of them a primary key--for instance a
city/state/zip together could be a primary key even though the same city
could be in many states, or the same zip could be assigned to many cities,
the combination of city/state/zip would be unique.

 

[BruceM]

This is an ongoing debate. I personally use autonumbers in most cases, even
if I don't see an immediate use for them. It's what I learned, and what I am
accustomed to using. People have maintained that by creating an unneeded
index (or something like that), it slows down performance, but I'm not
convinced that a combined PK is an improvement in that regard. I have heard
of instances where a company changed the format of an EmployeeID number or
some other number (by appending a 0, for instance, to allow for more IDs than
the current system allowed). Had EmployeeID been used as the primary key, or
as part of a combined PK, it could have been pretty messy.
Having said that, Order Number can serve as a PK if you like, since it will
be unique no matter if it is 1 digit or 10. You may want to look into an
incremented order number, in which 1 (or whatever you want) is added to the
number from the previous record. Try a groups search for "increment number
primary key" or "increment autonumber" or "simulated autonumber" or something
of the sort.
Use what works best for you. There is no single correct answer for every
situation.

 

[tw]

using an autonumber schema also helps if you ever need to know the "Natural
order" of the data.

 

[Brendan Reynolds]

I would not recommend depending on an AutoNumber field for date-entered
ordering, as this will fail if the database is replicated, when the New
Values property of all AutoNumber fields is changed to Random. True, most
databases will never be replicated, but as there are simple alternatives,
why take the risk, even if it seems to be a small one?

 

[Brendan Reynolds]

In all situations that I have so far encountered, simply setting the default
value of the date-entered field to Now() has been sufficient. You are
correct of course that this could be circumvented. I've never encountered a
situation where that has been a practical problem, and therefore have not
given any thought to it. Your proposed solution looks promising, but I have
not tested it.

 

[BruceM]

I'm not sure what you just said. What do you mean by "true key value"? What
is a mapping exercise. If I use employeeID as the PK I am not using a PK
that is likely to be included in OrderBy clauses, although I will group by
employeeID in order to have each employee's record on a separate page or
something like that. In that case I group by employee ID, and order by
FullName, which is a concatenated field in the report's underlying query. In
a company of about 70 employees there have been no duplicates in FullName,
but if there are I will order by something additional. My question to you is
what do you see as the advantage of updating the records if the PK value
changes in nature (as in the example of EmployeeID). Why is it not simpler
and cleaner to avoid that possibility? What would you use as PK in an
employee records database?

 

[BruceM]

I think I meant a 'natural' key. An autonumber is not a key. In your
brand new Employees table with its autonumber PK, insert a row for an
employee and you get ID=1. Then delete the row and insert the exact
same details again. Now you get ID=2. You have two different IDs that
refer to the same employee. Your ID column is not a key.

I'm missing your point here. If I delete the record that contains
autonumber ID=1 then there is no record containing ID=1. This would be a
problem only if I had related records in other tables.

You are aware full name is not likely to be unique, that's good.

I was referring to FullName as an Order By field in a report. As a
concatenated field in a query it can't have a PK in any case. Combining
FirstName and LastName as a PK would be risking duplication.

You proposed a theoretical scenario where a column (EmployeeID) needed
to be changed. Copying the data from the old to the new is what I'm
calling a mapping exercise i.e. you have to map (copy) the data from
each row in the old table into the corresponding row in the new table.

Got it. I thought you meant something like that, but wasn't sure.

Let me propose another theoretical scenario: something happens to your
employee table and your autonumber column is lost. You now have an
EarningsHistory table where each employee is identified using an
integer which has no meaning without the original values from your
Employees table. Consider the same EarningsHistory table where each row
had a unique identifier such as the aforementioned SSN, something
verifiable in reality e.g. phone the tax department and ask them which
employee has a certain SSN. Do you now see what I mean when I say I
feel 'happier' using a real key in the referencing tables?

Yes, I see your point about being able to reconstruct the database if the
SSN field is lost. Although I don't quite see how a single field can be lost
(a field involved in a relationship can't be deleted until the relationship
is undone) I think I would prefer reconstructing the PK field if it came to
that. As long as I keep the records ordered by the autonumber field I could
add a new autonumber field, then reorder the related table by the FK field
and replace all occurrences of the lowest number with 1, of the second lowest
with 2, etc. (or something like that). This is assuming my backup also went
bad.

I'm not trying to be contentious here. I really do appreciate your taking
the time to reply and to explain in further detail. I have a feeling that
neither of us is likely to change the other's mind, but you do raise some
points worth considering.