Tag Archives: Sql
Enum Considerations

I’ve been dealing with Enums and databases for awhile now. I think it’s something every LOB developer faces. Enums are developer-friendly because provide context to a delimited range of options in an easy to understand way. They do come with a couple of drawbacks however.

The first is that they’re not friendly for users. That Pascal-cased identifier looks great to a developer, but users aren’t really used to reading “RequireNumericData” and making sense out it. Since this post is supposed to be more about databases than UI, I’ll provide a quick snippet of code I use to get user-friendly descriptions of enum values:

public static class EnumService
{
    public static string GetDescription(System.Type enumType, System.Enum fieldValue)
    {
        var enumFields = from element in enumType
                             .GetFields(BindingFlags.Public | BindingFlags.Static)
                         select element;
        var matchingFields = enumFields
            .Where(e => string.Compare(fieldValue.ToString(), e.Name) == 0);
        var field = matchingFields.FirstOrDefault();

        string result = fieldValue.ToString()
                        .SplitCompoundTerm()
                        .ToTitleCase();

        if (field != null)
        {
            var descriptionAttribute = field
                .GetCustomAttributes(typeof(DescriptionAttribute), true)
                .Cast<DescriptionAttribute>()
                .FirstOrDefault();

            if (descriptionAttribute != null)
                result = descriptionAttribute.Description;
        }

        return result;

    }

    public static string GetDescription<T>(T fieldValue)
    {
        var fv = fieldValue as System.Enum;
        var result = GetDescription(typeof (T), fv);
        return result;
    }
}

I refer to two extension methods on the string class, SplitCompoundTerm() and ToTitleCase(). Here is the code for them:

public static string SplitCompoundTerm  (this string source)
{
    var chars = source.ToCharArray();
    var upperChars = from element in chars
                     where Char.IsUpper(element)
                     select element;

    var queue = new Queue<char>();
    upperChars.ForEach(queue.Enqueue);

    var newTerm = new List<Char>();
    var lastChar = default(char);
    foreach (var element in chars)
    {
        if (queue.Count() > 0 && queue.Peek() == element)
        {
            if (lastChar != ' ')
                newTerm.Add(' ');
            newTerm.Add(queue.Dequeue());
        }
        else
        {
            newTerm.Add(element);
        }
        lastChar = element;
    }

    var newCharArray = newTerm.ToArray();
    var temp = new string(newCharArray);
    var result = temp.Trim();
    return result;
}

public static string ToTitleCase(this string source)
{
    var result = CultureInfo.InvariantCulture.TextInfo.ToTitleCase(source);
    return result;
}

SlitCompoundTerm() bothers me a bit. All it’s doing is looking for upper-cased characters in the middle of the term and inserting a space before them. I’m sure there’s a way to do that with regular expressions, but I haven’t gotten around to figuring that out yet.

A second drawback is that you often have to decide how to store enum values in a database. Do you store the numeric value? The text? Do you create a table that contains both the numeric and text values? Would that be one table for each enum, or one master table for all enums and an addition “enum type” column? Storing the numeric value is nice because it’s easy, but it’s very hard to report on. If your enum values are binary, then a single numeric value is going to be great for searching. If you need to report on the data you really want text. Text creates its own issues because you can’t really do a search on all records with a state of “Pending | Ready”  using text. All of the developers problems are more easily solved by the numeric value, and all of the reporting problems are more easily solved by descriptive text. I’m sure some of you won’t like what I’m about to propose, but here it is: I store both values. Every enum value becomes not one, but two fields on the record in the RDBMS. From a data integrity standpoint, I can get away with this because I route all my data access calls through a well-defined data access layer. My apps never manipulate the RDBMS directly. While this approach solves my problems, it does come with the cost of increased data storage. This may or may not be an issue for you. It hasn’t been one for me so far.

Happy Coding!