Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a short (15 rows) look-up table that lists the valid values for several columns in the database.
I don't think it can be considered a One-True-Lookup-Table, it is about a single definite concept, but it may conceptually be partitioned in some subgroups (3).
I now happen to have to add a column that actually needs to accept only the values from one of these subgroups.

The proper thing to do now would probably to make one table for each subgroup and turn the original table into a simple list of IDs, from which the subgroup tables take their primary ids from.

It is though very unlikely that I ever further need to refer to the subgroups, I will instead frequently use things for which all the values in the original table are valid.

So the partitioning option would make things more complex for the vast part of the application just to support one case.

The only other option I know of is to add a Type column to the original lookup table and a fixed-value column to the single table that need to refer to the subgroup, and use a two-columns foreign key (to ID + Type) in this single case.

This is very ugly, and I'm not sure if it is theoretically correct to use columns that are not the primary key as foreign key referents, but given the context, is it acceptable, or even the best way?

share|improve this question

2 Answers 2

The proper thing to do now would probably to make one table for each subgroup ...

Probably.

It is though very unlikely that I ever further need to refer to the subgroups...

You probably said something very similar when you made that first questionable design decision. There's a reason that normalization never asks the question, "How often will I need to refer to whatever?"

If you don't enjoy fixing this kind of problem over and over, do it right today and get on with more useful and fun work.

share|improve this answer
    
Most data can be further partitioned in more specific data, as far as I know you should use objects only as specific as you need in your database. At first I didn't even notice you could partition that data. –  gab Apr 9 '13 at 15:14

At the end I avoided to use the solution with the Type column because it seems very unorthodox to put a foreign key that doesn't refer exactly the primary key of the other table; I did not make a table for each subtype though, only one for the specific one I needed with only one column with the appropriate list of the ids, leaving the general properties in the original table. This seems the best option since it is really, extremely unlikely I'll ever need to refer to the other subtypes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.