Look Up Tables - Best Method.....?? (Access 97 SR2)

[peterguk]

I find myself using lookup tables more and more, both for data integrity as well as efficiency, and have a general question.

Is it better to have in my main table a lookupID field which will be linked to the autonumber field in the lookup table, or a text field which is populated as a result of looking up the lookup table if you know what i mean <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

The main difference is obviously that the field in the main table will contain only integers, which must be better from both a storage and sorting point of view.

At present, i am of the opinion that it does not really matter, but i am interested in what other more experienced users would consider the "best" or "better" way of doing things.

Thank you for your time.

Peter

[charlotte]

With very few exceptions, I use autonumber keys in my lookup tables and insert that key into the target table. That provides maximum flexibility to handle things like misspellings or where someone decides the want a whole different phrase for a particular lookup value. Even a Year lookup table has an autonumber key that gets inserted into the year field because that way I can control the format (i.e., 4-digit year as opposed to 2-digit) without any extra effort.

My users aren't allowed to see the tables themselves, so only the DBA knows that the values they see are not literally stored in each record.

[MarkLiquorman]

>>I find myself using lookup tables more and more, both for data integrity as well as efficiency, and have a general question.

Is it better to have in my main table a lookupID field which will be linked to the autonumber field in the lookup table, or a text field which is populated as a result of looking up the lookup table if you know what i mean.<<


I'm assuming that by your 2nd alternative, your lookup table doesn't use an autonumber for the ID, just the text field (for example, a table of States would use the 2-letter Post Office abbreviation).

To tell the truth, I use both. If the lookup table text info is relative short and the values aren't likely to change, then I won't use an autonumber for the ID, but will use a text field as Primary Key (as in State table). But if it is a long text field and/or likely to be changed or it is likely to contain either an apostrophe or a quotation mark, then I will use an autonumber as PrimaryKey.

I figure I'm saving a little by not having to lookup the name each time if the ID field is text; that's just 1 less table I have to join in a query.

But that's just the way I feel about it.

[peterguk]

Thank you to both of you. Your time is, as always, appreciated <img src=/S/smile.gif border=0 alt=smile width=15 height=15>