Enumerations are popular in most modern programming lanugages because they do away with “Magic” numbers. A number has a strongly defined definition verified at compile time. Problems occur when you attempt to keep those enumerations synchronized with values in your database.
I was looking at the Enumeration data type found in MySql hoping to find a solution to Enumeration synchronization. My hope was that it could reduces the number of lookup tables necessary to complete a rather large database. In my mind this works well when the possible values only apply to a single table, and nothing more than a key value pair of int, string is necessary. If more than one table could use the values, then a lookup table is a better implementation.
I have not been able to find anything about the MySql enumeration data type allowing you to simplify the process of keeping code enumerations synchronized with the values in the database table definition. In fact, it appears even more difficult to extract the enumeration definitions from MySql than to gather the values from a reference table. In fact, it may not even be possible to do it programmatically.
I found an interested piece written by Chris Komlenic sharing his concerns with the Enumeration data type discouraging the use of the Enumeration data type except in a limited number of scenarios. I found his thoughts to be very informative about issues arising when you choose the Enumeration data type.
Perhaps you have some solutions you prefer. Please share your thoughts here or by Email to btaylor@sswug.org. How do you solve Enumeration synchronization? Have you found the MySql enumeration data type to work for you? Should other database vendors consider adding the Enumeration data type?
Cheers,
Ben