SQL: enum vs reference table when values are referenced in the code

Question

This question considers whether SQL DB data should be represented as an enum or by a reference table, when the application code is aware of particular instances of said data.

I'll illustrate with an example concerning user roles, but the question is more general. We could of course imagine other types of data than roles.

Imagine we are designing an application that has users and user roles. A user's role determines what features the user is authorized to access. The users and their roles are stored in an SQL database. The application is aware of the various user roles, i.e. role names like "admin" or "sales" are present in the application code. We might for instance see code like:

function can_see_sales_figures?(user):
  return user.is_admin? or user.is_sales?

This seems like a perfectly acceptable design to me. The issue arises when trying to decide how roles should be represented in the DB.

I can think of two main ways to do this:

1. An "enum" type value in a column of the user table itself. Either the DB's native enum type if one exists, or some string or int constrained to certain values.

   Users

   id	name	role
   1	'alice'	'admin'
   2	'bob'	'customer_support'
   3	'charlie'	'sales'

2. A reference table. We store the roles in a separate table, referenced by a foreign key in the user table.

   Roles

   id	name
   1	'admin'
   2	'sales'
   3	'customer_support'

   Users

   id	name	role_id
   1	'alice'	1
   2	'bob'	3
   3	'charlie'	2

My intuition says that, "philosophically" speaking, the only appropriate option to match our application design is (1).

With (2), we have hardcoded values in the application, that reference variable values in the DB. It feels like an antipattern to have the application code make assumptions about what values exist in the DB. The purpose of a database table is not having to hard-code values into the application code. For instance, it's perfectly reasonable and preferred to have a users table in our DB. For roles, however, there is only one set of reference table rows that makes sense from the application's point of view, namely those that the application recognizes. In my mind, we want to design our systems so that the application code is agnostic with regards to whatever rows of data populate the DB tables, which is clearly not the case with a roles reference table in this scenario.

With (1), the roles can instead be thought of as being "hardcoded" in both the application and the DB. The DB has a strict set of valid roles that can only be modified by performing a structural change (modify the role enum type or change role value constraints).

So, it seems to me that (1) is the appropriate option here.

Do you think I am right in this, or are any of my assumptions or my conclusion problematic?

As mentioned, please note that my question is not about authorization design in particular, but rather how we should generally use the DB to represent values that the application is aware of. My question also does not concern how well implemented enums are in various DB systems like PostgreSQL, MySQL etc.

Answer 1

With (2), we have hardcoded values in the application, that reference variable values in the DB. It feels like an antipattern to have the application code make assumptions about what values exist in the DB.

You have to have something defined in two places to get the two to talk to one another. There is no way around this.

The big advantage of a reference table is that you can maintain the human-readable relationship between "named" things in the code and "named" things in the reference table, but the Application can present these completely differently.
Borrowing your Roles table as an example:

select * 
from roles ; 

+---------+------------------+
| name    | description      |
+---------+------------------+
| ADMIN   | Admin            | 
| SALES   | Sales            | 
| CST_SUP | Customer Support |
+---------+------------------+

Nothing special there ... until your application goes international.

select * 
from roles ; 

+---------+---------------+
| name    | description   |
+---------+---------------+
| ADMIN   | Administrator | 
| SALES   | Umsatz        | 
| CST_SUP | Kundendienst  |
+---------+---------------+

No code changes. No recompilation. No Database Schema changes (to Enums) requiring administrative access to the database. Just regular SQL Update statements as part of the set-up to set the new, language-specific descriptions.

Answer 2

There’s fundamentally no difference between the 2 choices (and you skipped a 3rd choice having an independent table where the FK is the role name), with one major exception: a separate table (and I would call it a lookup table, not a reference table) allows you to define acceptable values.

Without this, you could have the role “”sells” “salles”, “sels” and whatever mistakes people make in your user table because it’s just a random string. If you are using MySQL it has an enum type that you define as part of the table, but then you have to redefine the table to make changes.

I would prefer a lookup table, and I don’t see any reason why it should be a string constant instead of a number, in particular because that will allow your non-sql code to have it as an enum. I might put constraints on the lookup table such that the name must be a valid identifier in my language of choice (forex no spaces).

Enums are like an interface. If it's not known by both sides of the communication pipeline, it doesn't make any sense. What would it mean for a user to have a role that the application didn't act on? What would it mean for the application to have a role that the database couldn't persist? They have to be duplicated and shared in order for communication to make sense.

BTW —- you seem to be limiting your users to one role each, typically people can have multiple roles and you use a cross reference (bridge) table to hold the actual roles that a person has.

Answer 3

users table with id to be used as foreign key

modified-users table using the users table foreign keys -- id (I use a unix timestamp-- modification date built in), user table foreign key id of user modified, field, old value, new value, user table foreign key id of user modifying

roles table for users using the users table foreign key -- id (again I use a unix timestamp-- creation date built in), user , role as text

modified-roles table...

i8n roles translation table per language key value pair, used when lang != base language

unit tests for UI will know if a lookup for unique role values fail to retrieve a translation key pair row in the i18n table

Complex reads on the database without preprocessing are expensive. Having lookup tables makes them more expensive.

It is just human hard to read. DB admins are used to it. Developers are given a challenge. Good luck with "the business", the non-technical people in general. Give a flat file output of any given table with a normalized role id instead of human readable text and then you have to give one or more lookup legends.

My perogative in doing things this way takes much more disk space and RAM. Performance testing proves the slightly reduced speed on one table-- but significantly faster speed for queries with a join and more savings with each additional join used.

Modified tables have allowed me to skip fixing things with the log. (Determine the value before the shenanigans, reset with a new correction entry row in the modified table.)

Also satisfies a lot of data "paper trail" reqs. --At the expense of HIPAA & GDPR extra fun when those come into play.

--This is often overkill. But, it also helps with reducing