enums in SQL Server database

sql sql-server database-design normalization
RKP picture RKP · Jan 9, 2012 · Viewed 12.8k times · Source

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

Answer

Olivier Jacot-Descombes picture Olivier Jacot-Descombes · Jan 9, 2012

  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.

Related questions

What does ON [PRIMARY] mean?

I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[be_Categories]( [CategoryID] [uniqueidentifier] ROWGUIDCOL NOT …

Read More
Relationship of Primary Key and Clustered Index

Can a TABLE have a primary key without a clustered index? And can a TABLE have a clustered index without having a primary key? Can anybody briefly tell me the relationship between primary key and clustered index?

Read More
SQL - How to store and navigate hierarchies?

What are the ways that you use to model and retrieve hierarchical info in a database?

Read More