Lookup tables - one or many

So i am designing a new database that currently has several tables 'look up tables' that are used just to limit the values of columns in other tables.

my question is what is the best way to do this?

1. multiple tables - one for each set of values (ex: JobType, Position, PayGrade)
2. One large table that holds all the lookup values - has a 'Category' field to group them
3. put constraints on the columns of the tables that are 'looking up' and get rid of the lookup tables.

thanks
lucas

Lucas, well you want lookup values in a table of SOME sort, so option #3 would be out for me. Much better design to have dynamic data changes, then hard-coding constraints.

Now the real choice... probably get half of the people using one way and the other half, the other... A lot of this depends on thigns like... what is going in your lookup tables. If it's a case where you have a relatively small # of values, and they are basically all the same (an ID, Code, Name Desc), then I prefer 2 tables, the Lookup and the LookupCategory tables. Doing it that way, the main issue is that you want a constraint back to the Lookup table, so you have to either have a 2 column PK there that you FK back to, or could use triggers to maintain the "FK" constraint, ehhh.. or just key back to the single PK column and have checks in place that they are FK'ing back to one of the values for the specific Category....

But if it's a small amount of Lookup tables, or they have more attributes then ID,Code,Name,Desc... then separate tables per Lookup works ok. If you start having 50, 70, 100+ of these 5 row type tables, that gets annoying.... but a lot of people can't deal with the object-oriented stlye, so it all depends on the developers and whoever will touch the tables, to see which of those 2 options to go with.... You'll probably get more people want to use one table per lookup, than the generic approach.... Bruce

|||Right now i have about 20 small lookup tables (just one field) with around 10 records in each.

i'm the developer as well and i am just planing on using these lookup tables to populate drop down lists in the application.

i'm starting to think i should have one large lookup table with a category table; like this:

LookUp
LookUpId (pk)
Category (fk)
Value

LookUpCategory
Category (pk)

look good?
|||

In that 2 table method, I'd have the columns for ID, Code, Name and Desc.... not just the value, at least some description field, if not NAME and DESC fields. On the Category, same thing, an ID, Code, Name, Desc.... If you just have a Category, unless it's a clear varchar code, then you won't know what it is exactly, especailly if teh model grows to 30,40,50 lookups... and, since you're FKing back to this table, might be better to call it tTypeCode and tTypeCategory... then your base tables would have fields called like propertyTypeID ( as opposed to propertyLookupID)... just clearer to call them type then lookups...

Other columns you MAY want to have in the type table are a Seq# and a defaultFlag....

FKing back to the Type table, is ok like that, but you could have a phoneTypeID value be stored that is really an ID for a propertyTypeID, unless you put some check in place to enforce the selection of type ID's by category...

Bruce

|||

If you just want to validate that a column

only contains a defined set of values don't need to do things like populate drop downs the set of valid values do not change