Keeping enums as look up tables in DB vs keeping them in code

/ Leave a Comment

I’ve worked in teams that do enums in 2 different ways. One is storing enums as lookup tables in the DB. The other is to keep it in code. I’m going to show how they are used and my preferred way. Disclaimer, this is just a personal preference. There are pros and cons in both ways.

The example enum I’m using will be UserRole

public enum UserRole
{
    [Description("Standard")]
    Standard = 0,

    [Description("Admin")]
    Admin = 1,

    [Description("Super Admin")]
    SuperAdmin = 2
}

Keeping enum as lookup table in DB

This is the way where we have a lookup table in the DB for the enum. This table consists of 2 columns, Id and Name which map to the values of the enum. This table will be populated as part of database seeding or migration. Other referencing tables will reference the Id column as foreign key.

Pros:
– Can show description of enum value without having to refer back to the code when querying data for reporting purposes.

Cons:
– Having to maintain the enum values both in code and in database. If the enum is updated in code, you need to update it in the database too. Removing an enum value that is not the last enum value (i.e Admin from above enum example) will be annoying as you would need to shift all enum values larger that the removed one by 1 if you want to clean up your data.

– Not very flexible if you need to add more metadata or functionality to the enum. For example, let’s say you want to have different role name per state, you would again need to store those in another table in the database and link it to the lookup table. You would need to add more effort in designing your database as more metadata get added. You would have something in the code to map to that table too so you have to maintain it in both places.

Keeping enum in code

This is where you don’t have lookup table for enum in the database. Everything is kept in code. The enum could be stored in referenced table as either the int value of the enum or the string value of the enum.

Pros:
– Better maintainability. Removing an enum value would still need a migration to set the old value to the new one  as required based on your business rules but adding a new value does not require any migration.  If you store enum as string in referencing tables, it eliminates the need to shift enum value in the referenced table when removing an enum value regardless of whether its the last enum value or not.

– Complete flexibility. Let solve the above requirement where role has different name per state.

public class UserRoleDefinition
{
    public UserRole Role { get; set; }
    public IDictionary<State, string> StateNames { get; set; } // State is an enum representing states in Australia
}
 
public static class UserRoleDefinitions
{
    public static UserRoleDefinition Standard = new UserRoleDefinition
    {
        Role = UserRole.Standard,
        StateNames = new Dictionary<State, string>
        {
            { State.NSW, "Standard" },
            { State.VIC, "Regular" },
            // ... and so on
        }
    };
}

Using the definitions will just be UserRoleDefinitions.Standard.StateNames[State.NSW] to get the role name in NSW. Your definition can be as complex as it needs to be without having to worry about the database.

Cons:
– Cannot show description of enum value without having to refer back to the code when querying data for reporting purposes. However storing as string will solve this issue. However, storing as string might have some performance impact if you reference it as foreign key in other tables but I never had to do this yet.

Conclusion

I prefer keeping enum in code since it makes it easier to maintain, no database migration required when adding new values and provide complete flexibility when you need to add additional metadata, functionality or behaviour to the enum. I see enum in database is just data, how it is interpreted or how it should behave is business logic and so should be kept in code.

Leave a Reply