Last week we talked about the value of having Enumerations in application code. The difficulty was keeping those Enumerations synchronized with the values in your database. There are a few ways you can address the synchronization problem.
- You can maintain the enumerations and reference tables independently, and use human intelligence to synchronize the values
- You can use the application enumerations as the authority, and synchronize your database to definitions found in code
- You can use the database reference tables as the authority, and synchronize your application enumerations to match the database settings
Option 1 is the most often used implementation. It is also the method that has prompted this editorial, because it relies on humans to be perfect. It’s just too easy to miss something.
The second option can be done manually, where you create the enumeration in your application, and immediately go to your database and create the necessary change script so that you don’t forget to synchronize. Another approach is simply to use a code first design similar to entity framework. In this case, you define your code, and using templates, the database changes are created and implemented.
The last option is the inverse of the second. In this case, it is again similar to entity framework. You make your database modifications first, and then utilizing a tool of some type, matching code is generated, keeping enumerations synchronized with the database contents.
Dilip shares a link http://www.developerhandbook.com/2014/02/15/use-t4-templates-to-create-enumerations-from-your-database-lookup-tables/ with a good example of using T4 templates to generate enumerations in an automated fashion from your reference tables. This is a good example of doing database first synchronization. The same technique can be performed using templates designed to use Code as the authority to define your database tables and contents.
I have created queries using data from information_schema views in SQL Server to generate C# code in the past. This is a little more manual than using something like T4 templates which integrate into Visual Studio, where you could be maintaining your code. But, it does result in accurate synchronization. I wouldn’t take the time to automate the query process because it only works in a database first implementation, and there are other implementations already automated. Since there are no added benefits, I don’t’ see any reason to re-create the wheel.
If you have some techniques specific to your world, ie. java, or different storage engines, please get into the conversation with your comment, or send an Email to btaylor@sswug.org.
Cheers,
Ben