=?Utf-8?B?SXJlbmFZ?= wrote in
:

I understand that I need to build table relationships based on
IDs, not on text values that may change over the life of the
database. I have read in this discussion group the perils of
value lists in a table. Here's my situation:

I hate to make things more complicated for you, but I am not sure that I
buy into your original premise. You seem to have a design like this:

Colours
ColourID Description
-----------------------
1 Red
2 Blue
3 White


Cars
CarNumber Colour
------------------
1024 1
1045 3
1190 1

etc. Now, this makes sense if the colour chart is really unstable; ie, if
all this year's Red cars will be Flaming Sunset next year. If, on the
other hand, there is relatively little changing of colours of cars, then
this model has a lot of advantages:

Colours
Description
-----------
Red
White
Blue

Cars
CarNumber Colour
------------------
1024 Red
1045 White
1190 Red

This means that you don't have to make any joins to see what colours the
cars are. Note that you would use referential integrity by making
Cars.Colour a FK referencing Colours(Description), so that users cannot
enter a non existent colour. It's true that using a text field like this
is marginally less efficient than a numeric field, but what you lose in
update speed (I doubt it's noticeable) you will gain hand-over-fist in
query speed (definitely noticeable, especially if you have eight look up
tables).

Just a thought


Tim F