Coming up with topics for daily editorials is an interesting process for me. Today I’m writing about keeping enumerations in code synchronized with reference tables, or enum columns in your database. I started with how to create an application where you can reasonably substitute one SQL relational engine with another. Then I was reminded that not all implementations are the same. Indeed, MySQL has an enum datat ype built into the engine which is not found in all other SQL engines.
So, now I am jumping to today’s topic, how to keep enumeration settings synchronized between your code and your database.
Probably a good place to start is to take on the question, “Why use enumerations in the first place?” From a code perspective Enumerations bring clarity. Enumerations remove the need to have a decoder ring to understand the meaning behind all the little magic numbers in your code. Let’s say you have an object in code, or a record in a table, having a property/column named status. In your code you set the status to 2. What does 2 mean? Go get your decoder ring…it isn’t clear. In fact, it may not even be consistent throughout your code, or in your database.
Having a status enumeration you can do the following:
public enum Status
{
Configuration = 0,
Active = 1,
Inactive = 2,
Deleted = 3,
}
In the database you would have a reference table with the same numeric values and descriptions, and it is a good practice to create a foreign key to the reference table assuring that a valid entry is always used in any table containing a status column.
Now you have the definitions in two places. They need to be synchronized so that the reference table has exactly the same values as those in the enumeration. You need to have the enumeration in the code so that the meaning is clear, and it implements the object oriented techniques for assuring correctness in usage, similar to what referential integrity does in a database.
How do you keep your enumerations and reference table definitions synchronized? Share your implementations with us in comments or by email to btaylor@sswug.org.
Cheers,
Ben