Hi Vin,

I use a single lookup table, with the following structure:

gLookupID     uniqueident (GUID)
cGroup        varchar(10)
cSubGroup1    varchar(10)
cSubGroup2    varchar(10)
iCodeOrder    smallint
cCodeValue    varchar( 6)
cShortDesc    varchar(15)
cDescription  varchar(60)
lActive       bit
lEditable     bit
vRelated      sql_variant

In this structure, cSubGroup1, cSubGroup2, and vRelated may be NULL.

Then I have a separate view for each group/subgroup1/subgroup2 combination that I need for any particular application(s). If one of those views needs the cCodeValue as another datatype, I'll CAST it in the view. This allows me not only the capability to "pseudo-normalize" the rows using the subgroups, but also to have a single front-end form to easily maintain ALL the values and not have to design separate forms for each lookup group.

This general structure/approach has worked well for me for several years, both in the VFP and SQL arenas. Carrying along the vRelated value is a new idea, forced by a particular application -- gives me a place to store a value that I might only need occasionally or with certain rows without having to have all sorts of different columns for different datatypes.

HTH.

>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