enums in SQL Server database

问题:

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)?

回答1:

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.

回答2:

As changing the enum inside your code will also require to release a new version of your application, I'd go for no lookup table at all, but only add a check constraint to the column in question.

If you however need to store localized names of the enum in the database, then I'd go for one lookup table for each enum.

The "one true lookup table" isn't going to give you any advantages.

Edit: if you need to dynamically retrieve the enum values (e.g. for a dropdown) or need to find out which values are allowed, then using lookup tables is probably better for you.

回答3:

Using the same table for several look-ups can come back to haunt you. One example is creating an Indexed View. This can help with performance if you have several fields where you want to display the lookup value, but SQL Server (at least 2005) won't allow you to reference the same table more than once (If this has been fixed, I would really like to know how to go about it because I could really use it in a current application with a single lookup table.).

One of your lookups may require an additional field, and by using one table, you'll have a lot of unecessary nulls. Indexing can be more flexible with separate tables. What if the new field needs to be required for one particular type of lookup? SQL Server will do well with a constraint, but when thrown in the same table a little more complexity is going to be needed.

You may not have any of these issues right now. I just don't see an advantage to a single table, but some apps like to have a dynamic way to generate lookup lists and won't create a new table for each one.