Enums and Lookup Tables

Everyone knows (or would know if they'd read Code Complete) that 'magic numbers' are a bad thing in your code.  Enumerated types, or Enums in .NET, are a great way to avoid such magic numbers.  For instance, let's say I have a content management system which has articles whose state can vary between Draft, Editing, and Production.  I could simply use 1, 2, and 3 for these states and remember what each stands for, but that's going to be really hard to look at six months later when I revisit the code (or if someone else has to read it).  In languages without enumerated types, named constants would be a step in the right direction, but truly enums are much more powerful because they allow me to limit the allowable values of parameters to only valid values.  For instance, I might have a method for updating the status of an article, called UpdateStatus(), which takes an integer articleId and an integer statusId.  There's nothing to prevent me from sending a -5 or a 300 to the statusId, which of course would not be valid status numbers, but are perfectly acceptable integers.  If instead I define the method so that it accepts an integer articleId and an ArticleStatus statusId, where ArticleStatus is an enumerated type, I'm assured that only valid values can be passed to that method (the compiler will enforce that).

Now, it's frequently the case that the database will contain so-called lookup tables, or tables whose sole purpose is to provide some information about another table through a foreign key.  In the above example, my Article table would likely have an ArticleStatusId which would be a foreign key to the ArticleStatus table.  ArticleStatus would simply have two columns, ArticleStatusId and Name, and would be considered a lookup column in this case.  These provide similar usefulness to enums, but at the database level.  By using a foreign key to this table instead of just an integer column without any constraints, I've ensured that only valid status ids can exist in my Article table.  I can also use the lookup table's data to populate dropdownlists in the UI to constrain the user's selection at that level.

Since they serve similar purposes, it would be great if lookup tables could be automatically converted into enumerated types so that developers could get compile-time strong type checking in their applications.  With Yukon's integration of .NET, we may be a step closer to this, but I'm not aware of any such feature coming any time soon.  So what often happens is enums and lookup tables are both used, and at some point they get out of synch with one another.  An additional status type, e.g. Archived, is added to the ArticleStatus lookup table in the database, but the enum is not updated (which requires the code to be recompiled).  This kind of thing will not cause any errors immediately, but it will come back to bite you at some point when you try to use the new value through your compiled application.  Some way of notifying you that the database's values had changed so that you knew to update and recompile your code is needed.

Although probably not the ideal solution to this problem, one solution I've devised is to write an enum-to-lookuptable checking unit test, using NUnit.  This test passes if the number of rows in the lookup table matches the number of items in the enum, and if each item in the lookup table has a matching value and named item in the enumeration.  An example of the test is shown here (note that this uses the Microsoft Data Access Application Block in addition to NUnit 2.1):

[Test]

public void EnumMatchesTableData()

{

Type enumType = typeof(AspAlliance.Data.Cms.ArticleStatus);

DataTable dt = SqlHelper.ExecuteDataset(AspAlliance.Data.Cms.SqlServer.Configuration.Current.ConnectionString,

CommandType.Text,

"SELECT * FROM ArticleStatus ORDER BY ArticleStatusID").Tables[0];

Assertion.AssertEquals("Enum count does not match database count.",

Enum.GetValues(enumType).GetLength(0),

dt.Rows.Count);

foreach(DataRow row in dt.Rows)

{

int id = Int32.Parse(row["ArticleStatusID"].ToString());

Assertion.AssertEquals(row["Name"].ToString().Replace(" ", ""), Enum.GetName(enumType, id));

}

}

7 Comments

  • Hmm... while you might be able to rationalize doing this, compile time checking against dynamic data isn't something you can rely on, so I wouldn't see much of a reason for it to ever make it into the compiler. In any case, you could argue that your code isn't doing this right in the first place. If you are going to use enums for status, it makes a lot of sense to pull the status name straight from the enum (enum.ToString()). If you need more data, or more descriptive names, try embedding string resources in your assembly instead of putting them in the database (one clear advantage here is internationalization). Now, how do you make sure there are no invalid values in the DB? That's easy. You don't need a FK in there, just put a check constraint on the column.

  • A good posting Steven. I took a cursory look at the use of SQL tables here and was wondering if the use of XML/XPath would suffice to do the same.

  • Actually, you can pass an invalid number to an Enum argument, but .NET lets you check to see if its valid or not:



    using System;



    public class MyClass {



    public enum MyEnum {

    One = 1

    }



    public static void Main() {

    MyFunction((MyEnum)7);

    MyFunction(MyEnum.One);

    Console.ReadLine();

    }



    private static void MyFunction(MyEnum t) {

    Console.Write(Enum.IsDefined(typeof(MyEnum), t));

    Console.Write(" ");

    Console.WriteLine(t);

    }

    }

  • Adam,

    Wow, never thought to try that. Ok, so you *can* pass them in, but you really have to try hard to do it and you *know* you're doing it. Since I would never intentionally do that, I'm still pretty happy to be using enums (though for my library code that others use, I think I will have to start doing validity checking of my enum inputs).



    Thanks,

    Steve

  • Interesting idea. But I am not sold on the idea of having having to maintain data in two places. In general, I prefer using Enum's and then doing something like Jesse Ezell recommends (e.g. using enum.ToString() ). I can see why you might want to do it because of strong-typing in your code and intellisense but the mere fact that I would have to maintain it in two places would keep me away from something like this.



    My 2 cents,

    -Mathew Nolton

  • Thanks for sharing.

  • Should you use an Enum in code or a Lookup Table in the DB for static lookup data. Should you use an Enum in code or a Lookup Table in the DB for static lookup data.
    ____________
    Lisa

Comments have been disabled for this content.