SQL Server and Enumerations

posted on 2005-03-05 at 00:40:26 by Joel Ross

Jeff, over on the asp.net blogs, asks how do you persist enumerations. So far, he has two comments. One does it by the numeric value, and one does it by the text value.

We do it by numeric value, but I like the reason for doing it by text - Steve says he does it because reporting is better with a readable field.

We actually have an interesting way to do most fields. We don't use Enumerations that much, because we want those properties to be tied to a lookup table. But we want to be able to code against those lookup values in certain cases. So what we did (good or bad, I'm still not sure) is to use a constants namespace, with classes for each lookup table. Each class has a constant for every value in the table. Now, these lookup tables don't change. Yes, I know the perils of saying something doesn't change, but we also have a Codesmith template that can regenerate the constants.

Now we get quite a bit of benefit from this. We get nicely named integers, since the constants are named based off of the description field in the lookup table. We get SQL constraint checks for those values, since we have foriegn keys to the lookup tables. And we get something we can code against when needed.

We still use enumerations for some things, but thier usefulness has been diminished by our use of constants.

Categories: Development