Numeric vs. Text IDs

[Josh]

I have some questions about Numeric vs. Text IDS.

Part I:

In short, is there a programatic reason why I should use
an numeric (autonumber) primary key for a lists that are
stored in tables? For instance, for a table of client
titles (President, Vice President, Executive VP, etc.)
that would be used to populate a combo box, is there a
benefit to using a numeric primary key as oppsed to the
actual tital being the primary key?

Part II:

What about if the list is joined to another list, such as
States and Counties

States

stateID -- autonumber (primary key)
state -- text

(with a one to many relationship on StateID)
Counties

CountyID
StateID
County

I've been encountering problems trying to move a filter
from a form to a report, because the state value is 33 as
opposed to "NY".

Am I just adding more work for myself by having this
autonumber as the primary key? Is it just as
programtically effecient to just use the state name as
the primary key and then have the state name and county
name together as the primary key.

Hope this made sense.

Thanks in advance.

Josh

 

[Roger Carlson]

Usually performance is cited as a reason to use numeric keys. I suppose
this is true, but for lookup tables the performance hit is probably
negligible.

There are 3 tests for a primary key:
1) How unique is it? Well if you decide what to call people, you can
control the uniqueness. If you can't, then it may be a problem. The state
abbreviations are unique, of course. Counties could duplicate, though.
2) How likely is it to be Null? Again, you control this with your titles
and the state designations will never be Null.
3) How likely are your values to change? Here you could run into problems
with your titles, but the states will be fine barring secession or a nuclear
strike or something.

The upshot is that there is no firm rule. It should be controlled by your
business rules. Most of the time, however, I've found that autonumber PKs
are the best solution.

 

[John Vinson]

I have some questions about Numeric vs. Text IDS.

Part I:

In short, is there a programatic reason why I should use
an numeric (autonumber) primary key for a lists that are
stored in tables? For instance, for a table of client
titles (President, Vice President, Executive VP, etc.)
that would be used to populate a combo box, is there a
benefit to using a numeric primary key as oppsed to the
actual tital being the primary key?

This is a contentious issue. Some folks always use Autonumbers; I will
often use text PK's *if* they are short, stable, and unique. Your
titles would be reasonable either way.

Part II:

What about if the list is joined to another list, such as
States and Counties

States

stateID -- autonumber (primary key)
state -- text

I routinely use the two-letter state code as the Primary Key. Yes, I
know, I know - Newfoundland and the Nortwest Territories in Canada
both changed codes over the past decade, but I can live with that
level of change.

(with a one to many relationship on StateID)
Counties

Counties are a much touchier issue. There are an astonishing number of
"Benton County" names in this country, apparently Mr. Benton was a
very busy surveyor back in the 1800's. There is a unique county code
list: see

http://www.itl.nist.gov/fipspubs/co-codes/states.htm

but even these are not unique nationwide, just within each state;
you'ld need the State code and the FIPS code (or county name) together
to ensure uniqueness.

CountyID
StateID
County

I've been encountering problems trying to move a filter
from a form to a report, because the state value is 33 as
opposed to "NY".

Well... DON'T then. Either use NY as the PK, or - MUCH more generally
- base your Report not on the Table, but on a Query joining your table
to the lookup table or tables.

Am I just adding more work for myself by having this
autonumber as the primary key? Is it just as
programtically effecient to just use the state name as
the primary key and then have the state name and county
name together as the primary key.

The two-letter state code occupies 2 bytes in the index; a Long
Integer autonumber occupies 4 bytes. Betcha the text field would be
FASTER in this case, though you'ld need to time it to milliseconds to
see the difference in a 50-row table.

 

[Joe Fallon]

I agree.
You will never notice the difference between a text PK and a numeric one in
an Access DB.
(SQL Server with millions of records might matter.)

It might just be simpler to use text values for lookups (and allow cascade
update but not cascade delete.)

The main benefit of a single integer PK is in QUERYING.
You can have a table with a triple field text PK (State, Count,Zip for
example).
If you use an integer PK instead there is only a single field needed to JOIN
tables.
This is simpler and faster.
--
Joe Fallon
Access MVP

 

[Mike Sherrill]

If you use an integer PK instead there is only a single field needed to JOIN
tables.

If you use natural keys, you might not need a JOIN at all.

 

[Joe Fallon]

Agreed.
A single field natural key reduces the number of JOINs because the raw data
is already present in the child table.
No need to JOIN to a parent by ID just to get the Description.

 

[Mike Sherrill]

Agreed.
A single field natural key reduces the number of JOINs because the raw data
is already present in the child table.
No need to JOIN to a parent by ID just to get the Description.

I know I'm nitpicking a little, but in general the number of columns
in the natural key doesn't matter. What matters is whether the
columns you need are part of that key.