I started down that path before and am apathetic toward it. It is convenient in that you only have to deal with 1 lookup table. Then it becomes a PITA when you bring up instances of numeric versus data. You could add another field to indicate the value that cCode really needs to be. This add a level of complexity when populating combos or listboxes. At the end, I finally just went with separate lookup tables.

As for storing the ID or the actual Value in the foreign table, I prefer the ID which then lets you to tweek the actaul descriptions in the lookup table without having to update all the values in the other tables. I would only store the description if it only matters what the description was at the time the user made the choice. Then you have to deal with descripts not in the lookup table especially if combos are used.

>I would really like to hear comments and impressions on a pretty basic design concept. I am designing a new database, with many lookup lists. I want to combine them into one table, so I was going to have a structure something like the following:
>
>idLookup (uniqueidentifier)
>cLookupID (varchar(6))
>cCode (varchar(6))
>cDescription (varchar(40))
>
>
>So, data might look like this:
>cLookupID -- cCode -- cDescription
>"State" -- "NY" -- "New York"
>"State" -- "NJ" -- "New Jersey"
>"Gender" -- "M" -- "Male"
>"Gender" -- "F" -- "Female"
>"Site" -- "A17" -- "311 West 17th Street"
>"Site" -- "C56" -- "23 East 15th Street"
>
>... etc.
>
>I generally like combining lookup tables like this, rather than having separate ones. BUT, what if my "site" codes are not A17 and C56, but actually numeric values -- 17, 56? Do I change the cCode to a Variant and CAST everywhere? Or do I force "17" to be the accepted value rather than 17?
>
>Or do I have three separate lookup tables?
>
>Thoughts?
>
>On a related note, although I keep a row GUID for each of the lookup values, I am considering breaking with database convention and storing the CODE, and not the GUID, in the source table. Or am I asking for trouble?
>
>-- Vin