Keeping Database Lookup tables and enumerations in sync

A common challenge encountered when developing applications is the synchronization between enumerated values in the codebase and static lookup table values in a database. This is an old problem that no good solution has been brought to light that I have found.

Today I was working on yet another implementation of this. I needed to create a lookup table in the database that contained the values for industry sector types. The entries in the table in the database each had a key and description. Within the code there was a matching enumeration where the numeric value of the enumeration matches the key value in the database. My concern is always about maintaining the two of them together, it is far too easy to update one without updating the other.

To try and alleviate this I used a unit test to keep them equal, at least in the most simplistic sense.

I used the Enum.GetValues(typeof(MyEnum)).GetLength(0); method to retrieve the number of values in the enumeration and then a COUNT(*) query on the table. Comparing the two results confirms that there are at least the same number of values in both the enumeration and the database. If an entry is added to either without the other then the unit test will fail.

This is a very simple solution that makes a rudimentary check, probably good enough for many situations and certainly better than not having a test at all. It wouldn't be too much more work to actually iterate over the values from each to ensure that the keys are a match as well. In our case though the descriptive field in the database is not quite the same as the string value of the enumeration so we can't take it any further.

If anyone has any other suggestions or experiences with keeping database lookup table values in sync with enumerated values I would love to hear them.