Do I really need separate lookup tables?

[KerryL]

Okay, I have an easy question.

If I have a relatively simple database with only 4-5 items that need to be looked up for combo boxes, can I put all the "look-up items" in one table?

For example, if I'm selling tires, tubes & rims, can I create a main lookup table with 4 columns:

ID Tires Tubes Rims
1 XRGa UV1 RS23
2 XRGb UV2 RS30
3 XRTc UZ1 RT12
etc etc

Do I need to put individual lookup items in separate lookup tables? Just curious.

 

[PHV]

putting individual lookup items in separate lookup tables simplify the RowSource of the combos.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

Do you think that you will have the same number of values to lookup about tires as you have rims and as you have tubes?

So you will need a WHERE clause to suppress the null values in a column with fewer values.

If you have three different lookups, then your alternative table structure is 1 table versus 3 tables. Does it matter that much?

If you extend your application to have a fourth lookup, you will need to modify the definition of the one lookup table by adding a column. I suppose that might be easier than creating a new table.

I once worked with an application that had one lookup table, with three columns, one each for the lookup type, code and description. This table structure never changes. When new properties are added to the application with new codes and descriptions, you add more rows to the existing table. It does entail a WHERE clause that filters the rows for the particular kind of lookup. But that actually worked pretty well.

 

[KerryL]

rac2,
No, there wouldn't be an equal number of values for each item. In my actual DB, if I were to put all my lookup data into one table, the columns would not be of equal length.

I was just asking because it seemed like a good idea to consolidate the lookup selections into one table instead of having 5-6 tables with just a few items in each one.

Sounds like it doesn't really matter one way or another.

In addition to the WHERE clause you spoke of, combo box properties would need to be set properly. Column width settings would all need to be set to zero except for the one column that contains the lookup items for that cbo.

Thank you for your feedback.

 

[lespaul]

Another option would be a single lookup table structured like:
[tt]
ID Description Type
1 XRGa Tires
2 XRGb Tires
3 XRTc Tires
4 UV1 Tubes
5 UV2 Tubes
6 UZ1 Tubes
7 RS23 Rims
8 RS30 Rims
9 RT12 Rims
[/tt]
HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[traingamer]

I often use tables similar to Leslie's suggestion.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

When you use a table like Leslie's do you create a WHERE statement for each cbo so that the available selection is filtered by Type?

That way the users only see what they're actually looking for; otherwise all selections in the lookup table would appear for every cbo. I wouldn't want the users to have to sort through the tire & tube listings ro find the rims.

 

[lespaul]

do you create a WHERE statement for each cbo so that the available selection is filtered by Type

exactly

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

do you create a WHERE statement for each cbo so that the available selection is filtered by Type
And now you perhaps understand better my post stamped 14 Feb 07 15:25.