select lists - DB best practice

rubyonrails-talk
1

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

2

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

3

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

4

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

5

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