Foreign Key constraint on fixed value field - Ever appropriate?

Question

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?

Answer 1

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.

Answer 2

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.