Storing Enumerations in the Database

Published: Fri 16 January 2009
By Jason


Given an enumeration (any non-bitmasked enumeration, really), how should members of this type be persisted in the database?

public enum ClockStyle {

ClockStyle.Digital will have an implicit numeric value of zero, ClockStyle.Analog will take the value of one. Should the database column be a string or numeric type?

The bit of research I've done on this finds most people favoring the string representation for the following (very good) reasons:

  1. It's more readable in the database.
  2. It doesn't break if someone inserts a new value into the array.

For example:

public enum ClockStyle {

If someone were to make this change in version 2 of this product, every Analog record would be converted to Sundial.

One could store numbers and mitigate the above problem by using explicit values; certainly a maintenance developer wouldn't dare change the explicit values. But explicit values are usually against coding standards.

“Avoid providing explicit values for enums unless they are integer powers of 2.”

-IDesign C# Coding Standard, Jan 2008 version, section 2, item 26

So just use the strings, right?

I don't like that solution either. I want to be able to refactor my code mercilessly, and if later I decide a different word expresses the intent of my enum value better, then it is going to be that new value. Here's my proposed solution:

public class EnumPersistanceAttribute : Attribute {
    public string StoredRepresentation { get; set; }
    public static string GetValueToStore<T>(T enumValue) {  }
    public static T GetEnumValue<T>(string storedValue) {  }
    // nice ctor goes here.

public enum ClockStyle {
    <EnumPersistance("Digital")> Digital,
    <EnumPersistance("Analog")> Analog

I like the way this works. I'm a little concerned about the way it looks. It's a little ugly.

A fix for the ugliness would be to make the attribute optional; GetValueToStore() and GetEnumValue() would look for the attribute; if it's missing then use the regular built-in string representation. Then you can go ahead and write enums the way you usually do (be sure to use the custom translation methods during persistence); you only need to use when you make a change that would otherwise break the storage. There are some drawbacks here:

  • Everyone must remember to use the translation in persistence for all enum types.
  • When you change an enum it would have to pop into your head to consider whether the change would break existing database records.

However, the last drawback isn't really relevant since it applies regardless of the strategy you use for persisting enum values.

Interested in any thoughts.

Comments !