Storing enum values in database

Question

FYI: I explicitly mean SQL Server 2000-8 and C#. So DBMSs with enum support like MySql is not the subject of my question.

I know this question has been asked multiple times in SO. But still, I see in answers that different approaches are taken to store enum values in db.

1. Save enum as int in db and extract the enum value (or enum description attribute using reflection) in code:
   this is the approach I usually use. The problem is when I try to query from database in SSMS, the retrieved data is hard to understand.

2. Save enum as string (varchar) in db and cast back to int in code.
   Actually, this might the best solution. But (don't laugh!) it doesn't feel right. I'm not sure about the cons. (Except more space in db which is usually acceptable) So anything else against this approach?

3. Have a separate table in db which is synchronized with code's enum definition and make a foreign key relationship between your main table and the enum table.
   The problem is when another enum value should be added later, Both code and db need to get updated. Also, there might be typos which can be a pain!

So in general when we can accept the overhead on db in 2nd solution, What would be the best way to store enum values in db? Is there a general definite design pattern rule about this?
Thanks.

Answer 1

There is no definite design rule (that I know of), but I prefer approach #1.

1. Is the approach I prefer. It's simple, and enums are usually compact enough that I start remembers what the numbers mean.
2. It's more readable, but can get in the way of refactoring or renaming your enumeration values when you want to. You lose some freedom of your code. All of the sudden you need to get a DBA involved (depending on where/how you work) just to change an enumeration value, or suffer with it. Parsing an enum has some performance impact as well since things like Locale come into play, but probably negligible.
3. What problem does that solve? You still have unreadable numbers in a table somewhere, unless you want to add the overhead of a join. But sometimes, this is the correct answer too depending on how the data is used.

EDIT: Chris in the comments had a good point: If you do go down the numeric approach, you should explicitly assign values so you can re-order them as well. For example:

public enum Foo
{
     Bar = 1,
     Baz = 2,
     Cat = 9,
     //Etc...
}

Answer 2

One idea I've seen before which is your option 3 more or less

• A table in the database (for foreign keys etc)
• A matching Enum in the client code
• A startup check (via database call) to ensure they match

The database table table can have a trigger or check constraint to reduce risk of changes. It shouldn't have any write permissions because the data is tied to a client code release, but it adds a safety factor in case the DBA bollixes up

If you have other clients reading the code (which is very common) then the database has complete data.

Answer 3

I referred to the answers here and on this question. I would like to summarize these answers and also add my perspective. So, here's what I think,

Option 1: Store the Enum values in a lookup table, but don't declare Enum in code

• In this option, you store the Enum values in a lookup table and use its Primary Key as a Foreign Key in other tables.
• Use this option when you want your "Enum" values to be dynamic and modifiable by the users at run-time.
• In this option, your code is completely dynamic and does not care about the "Enum" values.

Option 2: Store the Enum values in a lookup table and also declare Enum in code

• Like option 1, here also you create a lookup table but this time you also declare an Enum for that in code.
• Use this option when your business logic needs access to the Enum values but you would also like to provide referential integrity in the database or you intend to use the database with other applications also(ex. Data Analysis).
• Here you require to keep Data Sanity checks on the application startup to ensure that the Enum values in the code and in the database are in sync.
• You need to decide which one should be considered the "Source of Truth" between the database and the code and perform synchronization based on that.
• This method also has a join overhead as mentioned by some users.

Option 3: Declare the Enum in code, but don't create a lookup table for it

• Here, you declare the Enum in code and directly insert the Enum values in other tables instead of using the lookup table's primary key.
• Use this option when your business logic needs access to the Enum values and you do not intend to use the database with other applications. (In case you intend to do so, prefer option 2).
• You can store the Enum value as an integer or as a string (whichever you prefer).
• Here also you need some Data Sanity checks based on business logic. Example: If an Enum value is deleted from the code, no records should have that Enum value. Those records should either be deleted or updated with appropriate values.