First of all, to address the one lookup table vs many...

I'll start with my usual black-and-white statement that will eventually come back to haunt me :
"Overloading is one of the worst mistakes one can make in data modelling."

Overloading (in data modelling terms) is the practice of assigning more than one meaning to a column. In this case, one of the dangers is that you have lost your RI on these code values, since the 'code' column of this code table will now have code values for multiple usages. Now, it would be perfectly legal for a FK pointing to this table to hold a value of 'X' for state, assuming that 'X' existed in the code table for some other purpose. There are many more reasons not to do this:

- Some codes may have more attributes than others
- Some codes may be of different sizes or datatypes than others
- Some codes may be heirarchical (Country, State, County), and require their own FKs.
- Some code tables may end up being shared with or from other databases
- Much more readable data model (which I cannot over-emphasize)

If you have ever tried to maintain an old database where fields were overloaded, or worse - changed meaning over time, you will realize how complicated overloading can get.

Overloading..... bad.

Now, as to why we even need the little code tables...... we just do

Every column that ends in _cd should have a code table to go with it. If somebody wants to build a new application, or just use the data from a couple of our tables for another purpose, how would they know what the list of valid values are for a given field? Or what those values might mean? This is akin to using constants in your coding, but not providing the definition of those constants. So there is a field called Race_Cd but no race table. What are the valid race values that I can put in there? What does 'A' mean? American? American Indian, African, African American?

- Chris