Lookup Table design question

[Joe Williams]

If I have a lookup table consisting of a single field, should I need to have
an autonumber primary key in addition to that single field?

Currently I have only the one field, set as the primary key, with
referential integrity to the main table so if the field changes then it
updates the main table. Seems to work fine

In this situation, what is the advantage of the autonumber primary key?

Thanks

Joe

 

[tina]

if all the values in the lookup table are inherently unique, then you have a
"natural" primary key and probably don't need a "surrogate" primary key such
as an autonumber field. keep in mind that when a primary key is a value that
can and may be changed, then not only does referential integrity need to be
enforced in the tables' relationship, but also Cascade Updates needs to be
enabled. also, if you change an existing value in the lookup table at the
same time that other users are entering data in a table that uses that
value, then the cascade update may cause updating conflicts.

 

[John Vinson]

If I have a lookup table consisting of a single field, should I need to have
an autonumber primary key in addition to that single field?
No.

Currently I have only the one field, set as the primary key, with
referential integrity to the main table so if the field changes then it
updates the main table. Seems to work fine
Yep.

In this situation, what is the advantage of the autonumber primary key?

The *only* advantage is if you want to - rapidly - be able to change a
lookup value in the lookup table and have that value instantly change
in all records where it appears. With the two-field table the text
only appears in the lookup table so you only need to change it there.
If you use the one-field table, you must set Cascade Updates and it
will update every linked record if you change the field; this can be
timeconsuming and will contribute to database bloat. If it won't often
happen, don't worry about it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tim Ferguson]

If I have a lookup table consisting of a single field, should I need
to have an autonumber primary key in addition to that single field?

Scenario one:

LookUpColours
ColourID* Description
======== ---------------------------------------------------
0001 A deep tangy red, with hints of fire and warmth
0001 A hard scarlet, the colour of fresh blood and steel
0003 Delicate pink, like rare venison or new salmon..
etc etc

Advantages: easy to change and update descriptions; no risk of user error
due to misspelling; pretty fast joins on numeric keys; you can use the
numbers to create a custom sort order...

Scenario two:
LookUpColours
ColourCode*
=====
Red
Green
Blue
Black
Yello

Advantages: since the value stored in the child table (i.e. the FK) is
english-readable, you don't have to make a join query to find out what
colour things are; when required, joins are reasonably fast and efficient
on _short_ text strings; the DB engine won't let you misspell a FK value
anwyay;

So: you have to take your pick based on the needs of your database. I will
often use #2 when the description is short and pithy, like simple colours,
or Ready/ InUse/ Finished or whatever, particularly if I can mangle the
words so they appear in alphabetic order. For really static lists (like
Male/ Female/ Uncertain) I might even leave it buried in the ValidationRule
but I have to be sure it's never going to change.

Just another 2-p worth!

All the best


Tim F