DB Enumerations vs App Enumerations vs Lookup tables

Question

We started a project recently, and during the design process, we have some arguments among team members. We have to deal with enumerations so we ended up with three choices.

1. Have the enumerations as Normal Enums in Database
2. Have the enumerations inside the Application and isolated from the Database. Keep them only in App level.
3. Store the enumerations in different tables in the DB

What do you think and why? From my experience, I choose the 3rd, as I 've never seen that the cost of joins is really an issue. And if you use an ORM then the cost of joins is almost nothing due to caching.

Answer 1

I would suggest database tables, one per list.

The enumerations exist to list the permissible values for one or more attributes. Likely you'll want to enforce these restrictions within the database, too. For that you need foreign keys and for those you need to have the enumerations in tables. It is best to have one table per enumeration so the foreign keys are simple to define and obvious in their intent.

Having tables and foreign keys does not necessarily mean a join in every read. If the enumerations are stable over time and have short, meaningful codes then these codes can be propagated to the referencing tables. Say we have an enumeration of cardinal directions - North, South, East and West. These are stable over time. They have short, meaningful codes: N, S, E & W. Anyone seeing a code will understand the meaning given the context. Hence having the foreign key reference the single letter code will do the job and can omit a join. In contrast, the habitual design will have a surrogate key in the enumeration table. As this is typically a meaningless number, joins will still be required to return human-understandable values.

Once you have the enumerations in tables does it make sense to repeat them in the application? To repeat them as additional code would be a mistake. There would be two list - one in the app, one in the database - which have to be kept synchronized. Such duplication is a common source of error. To have the values cached in the application, as opposed to reading the table each time the enumeration's values are required, may be beneficial. It depends on the app, how often these enumerations are referenced, how much memory is available etc. etc. Since you're considering hard-coding the values, re-starting the app to pick up new values from the database shouldn't be a problem, though other solutions are possible.

Using database enumerations could work if you are very sure the list is single-valued and will never shrink. Removing an enumeration value is tricky. Adding new values requires elevated privileges. Additional properties (e.g. (direction, code, bearing) = (South, S, 180)) are not possible.

Answer 2

There is a dichotomy between two opposing viewpoints

1. Do you want the ability to add new items to the set without recompiling the application - this favours a lookup table in the database.

2. Is there any business logic based on the set within the compiled application. e.g.

   if (Client.ClientType == ClientType.Important)
       DoSomethingSpecial();
   

   This favours an enum within the application.

You have to decide which is most important to your case. Having both a lookup table and an enum, might seem like belt and braces security, but can soon cause issues if they get out of step.

Answer 3

If the cost of storage is trivial in each case, as you suggest, then the determinant would probably devolve to development and maintenance cost. Which means consider your available resources, and prefer the simplest and most familiar options.

Answer 4

You can store these enums as their integer values.

• Easy to do, low effort
• Low maintenance, no data sync issues, no deployment issues
• Can add CHECK MyCol BETWEEN 1 AND 3
• Best performance

If this simple solution satisfies your requirements then it's the way to go.

Sometimes, it is necessary to create enum tables. For example, you might need some associated value in a query. Maybe you have an enum for customer types and you want a query that uses the maximum order amount per customer type. Then, it's very convenient to have that data as static reference data available in the database for joining.

Don't eagerly create those tables. Do it when required. It is easy to upgrade from the integer column to the enum table solution.