Autonumber PK vs. Data PK

[OxDavis]

An easy question:

When I create dbs, I usually have to create single field tables that contain data that will never be duplicated, such as race, gender, etc. In the past I have just made the single "gender" or "race" field the primary key rather than assigned autonumber PKs to all the data. Is this the most efficient manner to do this? Are there any specific reasons I should be assigning autonumber PKs to this data?

 

[edtab]

One strong reason you'd design a table with automatic
PK's is to have some assurance that no record had been
deleted maliciously or accidentally. The continuity of
your primary keys (ie. no numbers missing) would be your
assurance that all previously entered records are indeed
in the table.

 

[OxDavis]

That makes perfect sense. Thanks for the input. Any other arguments pro or con?

 

[The_Doc_Man]

The problem with an autonumber PK is that it will NOT tell you if a number has been deleted. Autonumber is supposed to guarantee unique - but not monotonically ascending - numbers.

There is a simple rule to consider. If the PK's unique data value is equal to or less than 4 bytes, use the literal unique value. If it is more than 4 bytes, a LONG integer autonumber is shorter.

So for codes like race, gender, eye color, state codes, ... all of which could be easily represented by a one- or two-letter (=2 byte) code, I say store the raw code. When you get to people and unique identifiers, various reasons exist to use autonumber.

NOTE: When the item in question would have used the autonumber to convey meaning in and of itself, I would DEFINITELY avoid use of the autonumber. For example, a trouble-call system ticket number.

 

[Len Boorman]

Generally I use, if I can a meaningful PK. Now I know that this may not always be the most efficient in terms of storage but

1) If I use an Autonumber I still have to set a unique index on the value I want to totally prevent being duplicated.

2) As a developer (I hope) I am frequently tasked with ad-hoc reports. Tables resulting from decomposed M:M relationships with the two fields comprining the PK both containing integers I can handle okay but the additional overhead of having to also take another field that contains some meaningful description I find questionable.

I would not rank myself as an expert and produce applications purely for internal use. File size is not so far a problem area. I suspect by the time it is then a) I will be retired and b) a substantial number of the records will no longer be relevant and may be deleted/archived as the case may be.

I find myself agreeing with the proposal that what today is meaningful may not be so tomorrow but I am in Engineering. A Part Number has been a Part Number for some time, I expect it to continue.

Because of political correctness Christian name is maybe unacceptable but Forename seems rather neutral.

So my take on the subject is to consider each situation on its merits and go from there.

Must admit that when asked to review an application built by somebody else I
1) look for the Relationship Diagram. In none then Application is not then several black mark
2) Autonumbers as PK's are checked. Does another field have a Unique index. If not several black marks again

Len