Proper Use of Lookup Tables

[Keeper]

I have a table which contains information about personnel. There are several fields which I want to have consistent values inputted. For example, somebody might populate the "State" field with: California, CA, Ca, or C.A. To avoid this, I created a seperate lookup table with a list of all states, fully spelled out, and the digraph abreviation associated with each state. The digraph is the primary key for this new table. In all, I have 12 such lookup tables.

Having all these extra tables, while nice for clarity sake, seems excessive. In most cases, the lookup tables really only require one column, though I've always used a minimum of two (one is an acronym set as the primary key, and one is a fully spelled out description). I'm finding the primary key is often not useful to somebody reading the data; the full description is much better.



Am I using all these tables correctly? Could I have one catch all lookup table which combines all of the standardized fields which I want to use, and have no primary key for said table?

[orange]

Sounds like your approach is reasonable and recommended. When you have the 2 column approach, id and text fields, the id is a unique number, primary key and text is the descriptive name.
It is not recommended to have one all encompassing table with several, embedded look up tables, although some people do use such a scheme.

There is an excellent article concerning relational database and design at
http://forums.aspfree.com/attachment...achmentid=4712

[alansidman]

Here are two items that you may want to read.
http://www.deeptraining.com/litwin/d...aseDesign.aspx

http://access.mvps.org/access/lookupfields.htm

You should use lookup tables in forms and not in tables as noted in the article above.