When to create more than one look-up table?

[accessmatt]

I'm wondering whether it's ok to have all of my look up fields in one look-up table or, if not, when it makes sense to create multiple look-up tables?

[pbaldy]

That's a bit of a debate, but the consensus seems to be towards multiple tables, which is what I would do.

[accessmatt]

Thanks Paul! can you expound any on the logic of using multiple lookup tables? Seems superfluous to me.

That's a bit of a debate, but the consensus seems to be towards multiple tables, which is what I would do.

[JoeM]

I am with Paul on this one. I have multiple look-up tables for each category, i.e. I wouldn't put a "state abbreviation" lookup table in the same table as a "professional designation" lookup table. To me, it just more sense and seems easier to maintain to me. Not saying that you can't do it the other way, but I fail to see the benefit of doing so.

I usually also name all my lookup tables with a prefix of "ltbl" for lookup table (as opposed to "tbl", which I use for normal tables, and "ttbl" for temp tables). That way, they are all grouped together and easy to spot.

[pbaldy]

Thanks Paul! can you expound any on the logic of using multiple lookup tables? Seems superfluous to me.

Google "OTLT" or "one true lookup table" and you'll find lots of discussion. Here's one:

http://joecelkothesqlapprentice.blog...-question.html