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:
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.
id name role 1 'alice' 'admin' 2 'bob' 'customer_support' 3 'charlie' 'sales'
A reference table. We store the roles in a separate table, referenced by a foreign key in the user table.
id name 1 'admin' 2 'sales' 3 'customer_support'
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.