Love/Hate Relationship with Enums
Enums are used frequently to enforce a data domain and establish human readable names for codes.
An Enum only exists in application software. It does not exist in a database. Often we have reference tables in the database in order to translate codes created by application Enum values into human readable text.
Now we have the definition for a code value in two places. We have the definition in the database, and a definition in the Enum code. For an enumeration with a large number of possiblities it is easy to get the two different sources out of synch.
I have played with a number of techniques to translate the data found in a lookup table so that it may be used when programming, much as an Enum.
Here is an example of the Enum situation using the attribute Gender.
public enum Gender
{
Unknown = 0,
Female = 1,
Male = 2,
}
This could also be in a lookup table in your relational database.
CREATE TABLE Gender_Lookup (
GenderID TINYINT NOT NULL
,Description VARCHAR(10) NOT NULL
,CONSTRAINT PK_Gender PRIMARY KEY CLUSTERED (GenderID)
)
INSERT INTO Gender
SELECT 0, Unknown
UNION ALL SELECT 1, ‘Female’
UNION ALL SELECT 2, ‘Male’
This example is easy enough to keep synchronized since there are only three values, and the values are pretty much static. We haven’t had a new gender for quite some time.
We consume the contents of these two sources of data in different ways. The values from the table are often called to build dynamic controls such as drop down list boxes. They are also used in the database to validate referential integrity or to resolve the text value of the GenderID into human meaningful text.
Moreover, when dealing with multiple languages, a database is much more adept at replacing the description based on locality.
Still, any programmer knows the power of the Enum and the result in more accurate implementation of business rules as well as code readability.
What techniques do you use to simplify this dilemma? How do you maintain only one authoritative source for the available values, while still providing the verification during coding for selecting appropriate values (such as setting default values), or providing data sources for user controls?
Drop me an Email with any solution you’d like to share with our readers. Send it to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)
Featured Script
dba3_fx_BusinessDaysInRangeByComputation_Article
— Determine the number of business days in a given timeframe — by computation — Modeling Date Logic II: Queries, Functio… (read more)