select lists - DB best practice

The way you are doing it IS best practise. These types of tables are
commonly called "codes" or "lookup" tables as they are just
code/description pairs. I usually prefix them with LU_ to distinguish
them from the main application tables.

The other way to do it would be to have a single table for all codes by
adding a "type" column that would differentiate the type of code but I
would advise against it.

-Paul

Paul:

Could you please expand on why it's a best practice to use separate lookup tables? Also, why do you advise against using a single table?

I've used a single table in the past and want to know why it's sub- optimal.

Thanks,

-Anthony

Anthony,

Normalization is the short answer. Lets say you need to add an
effective_date column to one of your lookup tables because the code is
only effective starting on 1/1/2007 for instance. If everything is
lumped in one table all of your codes would have to take on that new
attribute. This might not seem like a big deal but now your table
encapuslates two different data models. Eventually you end up with code
that says "if type = this do that". Yes, you could just create a new
table at this point to model just the code that needs the
effective_date column and leave the existing table alone. But, that is
why it's best practise to start that way.

However, what you have done in the past WILL work within the
limitations just outlined.

-Paul

Paul,

Thanks for the explanation-- and the long answer! I appreciate stuff like this because it makes it easier to choose the correct way the next time I'm confronted with choices.

I have been using single table inheritance (STI) for my lookups. When something really different comes along-- you're right-- I do have to change to a separate table. The trade-off is that I don't have to repeat myself for the standard stuff: all my lookups have a display, key value, effective_date range, and active flag.

With many people throwing around DRY as a good idea, do you think that separate tables is always the best starting place? I think that for tables that have a low chance of being structurally changed, STI is a decent time saver. But, I've got to admit that my proverbial short cuts don't always pay dividends. What's your feeling on STI?

Thanks,

-Anthony

Anthony,

I have not used STI before but it seems rails provides support in the
framework to overcome some of the negatives I mentioned.That is, it
provides separate models for each entity as if they each had their own
database table and yet stores all of the data in a single table. The
main issue with it from my perspective would be that even though you
can split it into separate models you cannot designate for instance
that columns A and B are only to be used by model A and column C only
with model B. However, for something like simple lookup tables it may
have it's place. If your successfully using it in that regard I would
not change anything.

-Paul