lookup tables

[Jason]

Hello,

I was wondering what the best way is to use lookup tables. i don't want to
use a new table for every lookup.

Basically what i want is a generic lookup table for all kinds of data. In
queries, to translate the integer value of the lookup, it should know which
description it should use.

Anyone knows a solution?

 

[Rick Brandt]

Hello,

I was wondering what the best way is to use lookup tables. i don't
want to use a new table for every lookup.

Basically what i want is a generic lookup table for all kinds of
data. In queries, to translate the integer value of the lookup, it
should know which description it should use.

Anyone knows a solution?

I use a generic table for some lookups. Basically when I have enough items or a
need for on-the-fly changes to rule out a ValueList but not enough entries to
warrant a separate lookup table I use the generic table. For larger lookups
(more than 50 or so entries) I use a separate table. There are also times where
I want to avoid a WHERE clause on the right side of an outer join (often
required with a generic lookup table) so I will create a separate table.

A lot is determined by the size of the project. In my main app that I work in I
would need to add 30 or 40 more tables if all lookups had their own table. I
just wouldn't want to deal with all of that extra "clutter" for little added
benefit. In a smaller project I might need half a dozen tables to make all of
them separate. I would have no objection to that.

 

[Arvin Meyer]

I guess that, being a relational database purist, using a generic lookup
table bothers me. An Entity (table) describes or holds 1 thing and the
Attributes (fields) describe that 1 thing.

Be aware, that you'll always need to use at least 2 attributes to do your
lookup which also means you'll need to store both of those attributes with
each row of data. That can quickly eat up the efficiency of having just 1
table for all lookups. If there were long enough lists and/or slow enough
computers, the processing would be noticeably slower.

Personally, I'd use a single table for each lookup. YMMV.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

Be aware, that you'll always need to use at least 2 attributes to do your
lookup which also means you'll need to store both of those attributes with
each row of data.

ummm... not necessarily true. It's perfectly legitimate to have a
one-field table where the Primary Key is the actual value to be used;
the foreign key relationship is then not used to *find* the displayed
value but merely as a constraint to limit the choices to legitimate
values. For instance, a table of Categories could consist of just the
category name, and the parent table would have a text field for
Category.

John W. Vinson[MVP]

 

[Arvin Meyer]

ummm... not necessarily true. It's perfectly legitimate to have a
one-field table where the Primary Key is the actual value to be used;
the foreign key relationship is then not used to *find* the displayed
value but merely as a constraint to limit the choices to legitimate
values. For instance, a table of Categories could consist of just the
category name, and the parent table would have a text field for
Category.

Whether or not it is used as a constraint or in the find method, you still
need the extra category field. It is still needed in the lookup table to
describe the group to which the value belongs, or you can't limit the
choices to that group of values, or even show that group of values as a
choice. Normally a lookup table has only 2 fields, ID and text. With a
generic lookup, you will need 3 fields so that you can either use the 3rd
field as a constraint, or in the Order By clause to group the like items
together. I am still assuming that the Primary Key is still the actual value
used in the lookup.

Suppose you had lookups for both State and City stored in the same table
with 2 combo boxes on a form pointing to that table. How would you
distinguish "Washington" as a state in the state combo or city in the city
combo? Further, if you had a third combo with a name in it, how would you
know if "Washington" was a valid choice?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

Whether or not it is used as a constraint or in the find method, you still
need the extra category field. It is still needed in the lookup table to
describe the group to which the value belongs, or you can't limit the
choices to that group of values, or even show that group of values as a
choice.

True... if it's a field for which that is relevant!

Hypothetical example: titles. A table containing records for Mr.,
Mrs., Ms., Miss, and Dr. would not need any categorization.

Sure, it's not the most COMMON type of lookup, but there are cases
where you don't need more than the one field in a simple lookup table.
If you're going with a "polylookup" then of course you need two, or
even more, fields.

John W. Vinson[MVP]

 

[Arvin Meyer]

True... if it's a field for which that is relevant!

Hypothetical example: titles. A table containing records for Mr.,
Mrs., Ms., Miss, and Dr. would not need any categorization.

The few examples I can think of that would use a 1 field table would be
state or country codes. They're still unusual though because they usually
have a full name field as well as the abbreviation. I always use a value
list for courtesy titles like Mrs., Ms., Miss, since they almost never
change and generally have only a few entries.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Rick Brandt]

The few examples I can think of that would use a 1 field table would be
state or country codes. They're still unusual though because they usually
have a full name field as well as the abbreviation. I always use a value
list for courtesy titles like Mrs., Ms., Miss, since they almost never
change and generally have only a few entries.

Another area where I make a distinction when using my "catch-all" lookup table
is whether I consider the items in the list to be entities in my overall
application or not.

I agree that the use of a common lookup table can be viewed as violating pure
RDB practices, but I view them as nothing more than "easily editable Value
Lists". They do not represent other entities that I am tracking data on, but
are merely providing choices for a list control. In that roll I see them as no
more illegitimate than the use of a Value List in a form.