In a project that I have started working on, the database has a lot of FooDefinition tables which act like an enum. It’s a C# project using Entity Framework although that is not central to the question.
The entities have an ID, a string Name field and an int Type field that is converted to an enum. For example:
public class FooDefinition
{
public int Id { get; set; }
public string Name { get; set; }
public FooType FooType { get; set; } // this is an enum that defines the entity
}
The FooDefinition table is populated with an entity for each defined enum value, and does not change unless there is a new enum value added.
I am struggling to see the use in this pattern over just using the enum directly as opposed to joining on this Definition table. What are the possible benefits of this approach?
There are several issues that need to be resolved.
Categories
The usual use of categories is to identify classes or groups of records. If you create reports, having the values kept in a category table makes it easy for report generators or external data warehouses to properly categorize items.
Control Flavors
Very often, the values control the operation of the program, changing the interpretation of the data or influencing other decisions. For these, I tend to favor enumerations. Because these values directly impact the operation of the program, changing or adding values goes along with the new or modified code, so rebuilding and deploying the software is almost always required.
Descriptions and Augmented Values
Category values are seldom of interest to humans; you would never put up “1”, “2”, “3” in a drop down list to indicate “Forward”, “Backward”, or “Stopped”. Having a descriptive field to show user-selectable values corresponding to the in-program value is fairly important. Similarly, having a short-form (for drop downs) and a long-form (for report generators) is also quite valuable.
C#, to my understanding, does not allow augmenting the value of an enum. Recent versions of Java do, which I have found quite useful. For a C# implementation, it is likely best to use a category table for any such value that has an external representation.
Structured Categories
Categories are seldom unqualified. A category of PERIODICAL might have sub-categories of REFEREED and NONREFEREED. If your software needs to be able to gather data about periodicals, then you need a way to roll up the sub-categories. Using a table to hold the category listing, with parent-child relationships, is likely the best way to accomplish this.
Mix and Match
Often, I have combined both approaches for a category. Using an enumerated type, I can do switch statements and control the flow. Mapping the enumerated type underlying value to a database primary key requires a little care. This method is most useful when you need to regularly change the flavor text of a category, since that will not actually affect the operation of the program.
So, the content of the category table gets loaded at application start up. The enumerated type value can be used to index this table in the rare cases when you need to refer to an aspect of the category, such as the short name.
I see three reasons
1) As jimwise says, they can be changed without recompiling.
If they are actually used as enum values in application (do something if it equals that), only usefull thing to change without recommpiling is their name.
2) they CAN contain more than name and id, but two cannot have same id unlike C# style enums
3) they can be used in queries/views to make results more human readable.
Its better to see request with state failed than with state 3.
The main benefit I see is that you can add new enum values with a database operation, without compiling and deploying a new version of your application. By treating the set of “Foos” which may exist as data, the impact on the running application of adding new types of “Foo” is kept small.
I actually just had to make a change that linked two of these tables together, which would have had to have been done in code otherwise (and then required a code release whenever this relationship changed). So I suppose one reason is extensibility in saving data about these enums.
I’ve seen this used in Microsoft Dynamics CRM. It allows users to customize dropdown lists and the like quickly and easily on the fly.
Unless you intend an application to be extensively modifiable by users, I’m not sure if you would want to go down this route, as it could lead to some extra complexity to working with the data from the back end.