What is the best desing for configuration tables with simple FK? [closed]

Question

Have configuration tables with 6 columns (name ImpData). One of column is a column with Priority. Could have value

HIGH,
MEDIUM,
LOW

What is the best approach for solve this requirment:

1. Create table with one column with above values and create FK in ImpData table.
2. Create CHECK Constraint in ImpData table which will allow only above values ?

Insert to configuration is possible only by Sql Server administrator. In future .NET application will be created.

Answer 1

This is "opinion based" in the sense that there is no hard-and-fast rule for when to use a check constraint rather than a foreign key for a small enumeration. Both patterns are common and work fine.

Foreign keys enable you to read and modify the enumeration from a client application with non-admin permissions; so if you anticipate needing that, use a foreign key. But if you start with a CHECK constraint, you can switch to a foreign key later.

Answer 2

Since this is mainly a matter of opinion, I'll give you mine, with the caveat that both approaches you outline will work.

I've learned the hard way to always go with the FK into another small enum table. Moreover, that enum table should be a proper table, with its own id. So the setup I'd recommend is:

table Priorities:
id | Value
1  | HIGH
2  | MEDIUM
3  | LOW

With table ImpData's Priority field = 1, 2, or 3.

For sure, this entails a slight performance hit and increase in query complexity as you'll need to add a join each time. So why do I recommend it? Because I've found that as a project grows, what begins as a simple Enum often ends up becoming a complicated record in its own right. Maybe you want to add additional values, or change a value. Or maybe this value starts getting displayed on a website, and then you'll want to have different language versions for the same priority, plus specify which color to use for each one. In a few months, you may need to add a priority field to another table, and want to use the same Enum to make sure everything is consistent.

All of these things are super easy if you have a proper table, with a proper PK, and proper FK relations. OTOH, if you start with a simple constraint check, you're depending on the fact that your schema will never change. Maybe that's true if you're dealing with some legacy DB that hasn't changed since the COBOL days :-) But if you're starting a new project, I'd vote for flexibility in your schema over a slight performance hit any day.

A compromise would be to avoid using a PK and just make the values themselves the PK. The main benefit of this is that the value is right there in the ImpData table, and so you can see it easily and avoid a join. The downside is you can never have duplicate values. That might sound like a pretty safe assumption (and in this case, it probably is). But you never know when Murphy's Law will hit. In this case, you've set your PK on English words. You better hope there aren't synonyms in other languages that you might need to differentiate in the future :-) For example, maybe in French, two different priority levels end up mapping to 'HIGH' in English. Or maybe you want to have 7 different colors to indicate different priority levels, but on a text-interface, you want to map them into only 3 different words (LOW, MEDIUM, HIGH). If you have a separate PK, this is trivial to deal with. If you use the English values as PKs, you have a problem.

Admittedly, these are contrived examples, but hopefully you get my point. You never know what will be needed from your DB in the future, and keeping things as flexible as possible (when it's feasible to do so without drastically increasing complexity or performance issues) is, IMHO, the better option.

Answer 3

CHECK option for literral il very great when two conditions are met :

1. there is a fixed number of values independent of any business functionality (as an example, cardinality point East, West, South, North).
2. you don't need to migrate this database to another language or another culture

Otherwise, use an external reference table

The only hot point with CHECK constraint is the semantic ordering of the values which is not often correlated to the alphabetic one. In your example any ORDER BY on the litteral "HIGH", "MEDIUM" and "LOW" will be false regarding to the semantic consideration. So you will need to use a CASE operator to do so.