enums in SQL Server database

Question

Is there a better or easier way to store the enums (Enumerations available in programming languages like C#) in SQL Server database other than simply creating a lookup table (with Id, code and name as columns) for each of them (especially when there are very few rows in each of those tables)? I found an article that suggests creating just one lookup table for all enumerations and the approach is criticised by some people in comments saying it violates referential data integrity. if at all the enumeration is used by only one table, is it a good practice to use some predefined codes and then add a constraint for them (may be using extended properties)?

Solution

1. Personally, I like to define one lookup table per enum, because it is a kind of documentation as well. If someone wants to know what an id stands for, he would find it easily in a table. Looking for this information in a column constraint is not evident.

2. It is also much easier to add new values in a table than in a constraint.

3. If you create a database diagram, individual lookup tables appear more logical.

4. You can add additional information to individual lookup tables besides id and text, if required (like a comment, a sort column, some sort of flag etc.).

5. And as you have said, it is better for referential integrity

However; if you are using an o/r-mapper with the code-first approach, using enums provided by the programming language feels quite natural. This is because you are not designing a database but an object model. The o/r-mapper creates the database automatically for you.

Comments

[Mike Sherrill 'Cat Recall']

"One lookup table for all enumerations", aka "The One True Lookup Table", aka "OTLT", is a well-known SQL anti-pattern. You can Google that. I'd argue that enumerations themselves are a database anti-pattern, because they don't easily accommodate extension (additional attributes).

[RKP]

I haven't got any localization requirements. only problem with check constraint is that that can be used only if the lookup data is used only in one table and also user of the table will have to look into the schema definition of the table to see the permitted codes and description for that column

[a_horse_with_no_name]

If you need to see the allowed values using SELECT statements, then a lookup table is probably better for your. Regarding the "can only be used in one table": it's been a while since I used SQL Server. In PostgreSQL I would define a new domain that encapsulates the check constraint and makes it re-usable. Maybe something similar is possible in SQL Server as well.

[Olivier Jacot-Descombes]

Even if the displayed names are generated in code, it can be useful to have them in a table, as they could be used in some reporting tool. Even if you are just executing a query in the sql server manager in order to check something, the names can be useful.