More Code Lookup Options for Enumerations
Following are more ideas for synchronizing Enumerations with database lookup tables. The goal is to have Enumerations usable in application code, validated at compile time, that have the same values as those of lookup tables in external data stores such as relational databases.
Many have written to remind us that MySQL an other data engines support the Enum Data type as a valid column definition. While this practice is really cool, it doesn’t resolve the prolem of keeping Enum values stored in a database in synch with Enum values used to write and compile code.
As an aside note, I have discontinued using identity columns for lookup tables; especially if that table may be used for an enumeration. By having to assign values, I have no consern regarding the order of record creation, assuring that the number assigned matches the desired description. This is especially helpful when working with multi-developer or multi-DBA teams performing multiple efforts concurrently. In fact, Enum values may be assigned prior to creating a table.
The best ideas I have heard so far all spawn from a central concept that your data store should be your authoritative source. Enumerations are kept in synch with a number of techniques that follow.
Herbert Writes:
The issue of synchronizing constant definitions between SQL Server and C# troubled me from the moment I started developing .Net programs.
Actually, it was one of the reasons why I created my application called dbscript..
In my approach, the database is always the master, and C# constant definitions are generated from the database contents.
See my blogs on this subject
http://devio.wordpress.com/2008/12/29/generating-c-const-declarations-from-table-data/
http://devio.wordpress.com/2008/12/29/generating-insert-and-update-statements-from-table-data/
http://devio.wordpress.com/2009/11/23/constants-in-ms-sql-server/
Jens writes:
We have a web interface for managing all enums and const. The web stores and read from the table and the generate the code for the different platforms – we use c#, delphi and vb-script.
So we alter the code through this interface so only one place for these kinds of data
Chris Writes:
When dealing with situations where I have a known quantity of set values that I also wish to have stored in the database (or made available via drop down lists, etc.), I will usually go with a static construct such as below:
public class DwellingType
{
private DwellingType(string FriendlyName)
{
_friendlyName = FriendlyName;
}
public override string ToString() { return _friendlyName; }
public static DwellingType Apartment = new DwellingType("Apartment");
public static DwellingType House = new DwellingType("House");
public static DwellingType Mansion = new DwellingType("Mansion");
}
resulting in a usage of:
var myHouse = DwellingType.House;
Brian Writes:
Thanks for keeping these good discussions going with SSWUG.
I’ve tried several different ways to manage lookup tables and corresponding enumerations without pulling out my hair! One of the most relevant techniques I’ve found in my work with SQL server, legacy code, and .NET, has been to choose my primary key type carefully. I’ll use a look up table and enum with an integer-based primary key if I need to use the IDENTITY clause to automatically insert sequential primary key values or if I need to have an integer for some other reason. But otherwise, a varchar-based primary key in my lookup table eliminates the need for an enumeration in my business logic code because I no longer have mysterious primary key integers that are meaningless without an enum. A varchar takes up more bytes (length+2 bytes) than an integer (1 byte for tinyint, 2 bytes for smallint, 4 bytes for int), but in some situations, that’s a worthwhile trade-off.
Byron Writes:
Not to nitpick, ISO 5218, “Codes for the representation of human sexes” actually defines 4 codes, 0, 1, 2, 9 for unknown, male, female, and N/A respectively. Given some countries recent changes, I can see where at least one more code could be added soon for transgender people, or those that do not claim a specific gender. What that might be I don’t know, but I can see application code having to be changed all over the place.
Having said that, I have used various techniques, depending on the application, but I’ve yet to find a good replacement for using a combination of the database and enums in code. At one point I tried routines that validated the enums against the database at application startup, but quickly abandoned that approach.
If you have an idea for synchronizing enumerations with your data stores that works better for you, share it with us by sending your comments to btaylor@sswug.org.
Cheers,
Ben