Database Design – Definition Entities vs Enums in C#

cdatabasedatabase-design

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?

Best Answer

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.

Related Topic