Storing enum values in database

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 tak开发者_如何学Pythonen 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.

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...
}

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.