Thursday, March 06, 2008

Named constants and Enums: why and how to reconcile them against database tables?

Enums are very convenient, because they make code developer-friendly. If a method takes enum parameter, IntelliSense would help developer to choose a correct parameter value (and compiler would catch passing incorrect integer). On the other side, it is often a good idea to put put corresponding values into database lookup tables. It would allow to use primary/foreign keys to ensure data integrity (suppose, that your application is not the only way to read or modify data, so you want to be sure data is correct). Question is, how to synchronize enums with database?

Because VBScript does not have named enums, I used named string constants instead. One technique I employed in my Roles Rights Management (RRM) system was to auto-generate VBScript constants by reading lookup table and using eval() function during Application startup. It's much more developer-friendly to allow calls like

RRManager.HasRight (cnstCanSeePage, ControlNumber)

than to force a developer to use numbers like

RRManager.HasRight (1, ControlNumber)

Again, in C# we would use enums instead of string constants. The question is:
* is it better to auto-generate those VBScript string constants (or to auto-generate C# enums; I hope it's possible) from database values or is it better to reconcile hard-coded string constants / enums against database values on Application startup? What are ramifications of using each approach? *
If I remember correctly, Imar Spaanjaars also touched upon this issue in a discussion beneath his article.

Auto-generation of enums might be dangerous. Suppose that someone deleted a row from a database and as a result enumValueOne is not generated any more. Then, if another programmer calls AnObject.DoSomething(enumValueOne) application would probably crash.
On the other side, if you don't auto-generate, but rather reconcile enums against database on Application startup, and someone deleted a database record, your Application will immediately tell you about that problem and just won't start. It's safer. But it's less convenient to use this approach if available enum values changes frequently: now you keep essentially the same data in two places, so you have to modify code every time you add or delete a record from database. Not good!

I think the answer is:
- If a list of possibilities is fixed and is not going to be changed frequently, do not auto-generate Enumerations. Instead, reconcile them against database values on Application startup.
- If a list of possible enum values changes frequently, use auto-generation from a database.

What do you think?