Enum storage in Database field

Question

Is it best to store the enum value or the enum name in a database table field?

For example should I store 'TJLeft' as a string or a it's equivalent value in the database?

Public Enum TextJustification
  TJLeft
  TJCenter
  TJRight
End Enum

I'm currently leaning towards the name as some could come along later and explicitly assign a different value.

Edit -

Some of the enums are under my control but some are from third parties.

Answer 1

Store an ID (value) and a varchar name; this lets you query on either way. Searching on the name is reasonable if your IDs (values) may get out of sync later.

Answer 2

It is better to use the integer representation... If you have to change the Enum later (add more values etc) you can explicitly assign the integer value to the Enum value so that your Enum representation in code still matches what you have in the database.

Answer 3

It depends on how important performance is versus readability. Databases can index numeric values a lot easier than strings, which means you can get better performance without using as much memory. It would also reduce the amount of data going across the wire somewhat. On the other hand, when you look at a numeric value in your database which you then have to refer to a code file to translate, that can be annoying.

In most cases, I'd suggest using the value, but you will need to make sure you're explicitly setting those values so that if you add a value in the future it doesn't shift the references around.

Answer 4

As often it depends on many things:

Do you want to sort by the natural order of the enums? Use the numeric values. Do you work directly in the database using a low level tool? use the name. Do you have huge amounts of data and performance is an issue? use the number

For me the most important issue is most of the time maintainability:

If your enums change in the future, names will either match correctly of fail hard and loud. With numbers some one can add a enum instance, changing all the numbers of all enums, so you have to update all the tables where the enum is used. And almost no way to know if you missed a table.

Answer 5

Another reason to store the numeric value is if you're using the [Flags] attribute on your enumeration in cases where you may want to allow for multiple enumeration values. Say, for example you want to let someone pick what days of the week that they're available for something...

[Flags] 
public enum WeekDays     
{
   Monday=1,
   Tuesday=2,
   Wednesday=4,
   Thursday=8,
   Friday=16
}

In this case, you can store the numeric value in the db for any combination of the values (for example, 3 == Monday and Tuesday)

Answer 6

I always use lookup tables consisting of the fields

• OID int (pk) as the numeric value
• ProgID varchar (unique) as the value's identifier in C# (i.e. const name, or enum symbol)
• ID nvarchar as the display value (UI)

dbscript lets me generate C# code from my lookup tables, so my code is always in sync with the database.

Answer 7

For your own enums, use the numeric values, for one simple reason: it allows for every part of enum functionality, out of the box, with no hassle. The only caveat is that in the enum definition, every member must be explicitly given a numeric value, which can never change (or, at least, not after you've made the first release). I always add a prominent comment to enums that get persisted to the database, so people don't go changing the constants.

Here are some reasons why numeric values are better than string identifiers:

• It is the simplest way to represent the value
• Database searching/sorting is faster
• Lower database storage cost (which could be a serious issue for some applications)
• You can add [Flags] to your enum and not break your code and/or existing data
• For [Flags] stored in a string field:
  • Poorly normalized data
  • Could generate false-positive anomalies when doing matching (i.e., if you have members "Sales" and "RetailSales", merely doing a substring search for "Sales" will match on either type). This has to be constrained either by using a regex on word boundaries (finicky using databases, and slow), or constraining in the enum itself, which is nonstandard, error-prone, and very difficult to debug.
• For string fields (either [Flags] or not), if the database is obfuscated, this field has to be handled, which greatly affects the ability and efficiency when doing searching/sorting code, as mentioned in the previous point
• You can rename any of the members without breaking the database code and/or existing client data.
• Less over-the-wire data transfer space/time needed

There are only two situations where using the member names in the database may be an advantage:

• If you're doing a lot of data editing manually... but who does that? And if you are, there's a good chance you're not going to be using an enum anyway.
• Third-party enums where they may not be so diligent as to maintain the numeric value constants. But I have to say, anyone releasing a decently-written API is overwhelmingly likely to be smart enough to keep the enum values constant. (The identifiers have to stay the same since changing them would break existing code.)

On lookup tables, which I strongly discourage because they are a one-way bullet train to a maintenance nightmare:

• Adding [Flags] functionality requires the use of a junction table, which means more complicated queries (existing ones need to be rewritten), and added complexity. What about existing client data?
• If the identifier is stored in the data table, what's the point of having a lookup table in the first place?
• If the numeric value is stored in the data table, you gain nothing since you still have to look up the identifier from the lookup table. To make it easier, you could create a view... for every table that has an enum value in it. And then let's not even think about [Flags] enums.
• Introducing any kind of synchronization between database and code is just asking for trouble. What about existing client data?