Enumeration Synchronization With Application Code

Keeping Enumerations synchronized between application code and database lookup tables is simply painful. I have played with the idea of creating my lookup tables needing to be synchronized with three columns, ID, Description, Enumeration.

I’m working on a T4 template that will generate Enumeration C# code from these tables so they can be kept synchronized with no special effort. The template would use the Enumeration column for the name, and the ID field for the setting.

Tony write in with similar concerns:

In SQL Server I have accomplished enumerated types 2 ways:

1. Create a read-only table for each enumeration I need: ENUM_Months (for example) which has 2 fields: id and Name. I then have a pre-defined set of values to check against and I can look values up by name.
2. Create a user-defined data type (say based on TINYINT) with constraints on values. This works ok except for cases where you want to treat the values as flags for bitwise combination in which case you would need to use the base type. Additionally, these constraints seem only to be enforced in tables with columns of this type, i.e. not if you use them in stored procedures. Not to mention that if you ever needed to change the definition it might not be as straight forward as you might think.

So, I would argue that the devil would be in the details. I would like to use them in certain cases but they would have to be more robust, flexible and straight forward to change. .Net has a nice way of declaring enumerations which can be bitwise flags; however, .Net doesn’t persist data; if you change an enumeration everything that references it gets updated with the next compile. The challenge here is that when you change the enumeration definition in a database that can potentially cause data corruption with persisted data by breaking referential integrity to prior values of the enumeration. Of course, you mentioned the disconnect between enumerated values in the database and maintaining matching definitions in client applications. So, maybe a nice feature would be keep enumerations defined on a server that both the database and client can access; but again, if you change it on the server that could break existing code and databases. So, the whole notion is a very messy proposition.

Do you have a solution for synchronizing Enumerations with lookup tables? Share your thoughts here online, or drop an email to btaylor@sswug.org.

Cheers,

Ben