3

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.

2

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.

1

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.

2
  • 1
    I don't quite agree with the point of acceptable values. We would define what values are allowed to be entered, by using enum or check constraints. Allowing any value would ofc be bad. My main point was that, with a lookup table, what roles are available is a matter of data, whereas with some type of enum, it's a matter of structure. Philosophically, an enum feels more like a data type than it does a variable value. It makes sense for the application to assume that the DB has a certain structure, but doesn't it make less sense for it to assume that it has certain values?
    – Moeghoeg
    Jul 15 at 12:54
  • (as a matter of fact, in PostgreSQL enums are defined as their own datatypes. So "role" would be a datatype just like Integer or Varchar, only with a few allowed values) Regarding multiple roles, we could have a separate table "assigned_user_roles", with a foreign key to the user and an enum value signifying the role.
    – Moeghoeg
    Jul 15 at 12:55
1

I think you are in this position because you are combining both "permissions" and "roles" into just "roles".

You could split the two, and add table role_permissions:

id role permission
1 admin *
2 sales_admin sales.*
3 sales_user sales.reports.*
4 sales_user sales.dashboard.*
...

What this table does essentially is serve as a seam between the permission system in your application code-base, and the permissions that you then configure to users in the database. Although this is quite confusing in terminology, you could very well use the role names in your application code in the permission field, what matters is that there is this seam.

This would leave you in a situation where the app doesn't need to hard-code the roles in a matching fashion to the database, leaving flexibility in the design and much less risk of inconsistency.

You might argue that this just pushes the envelope down to where you now have to hard-code the permissions. Which is true, but a key difference here is that with this design, your app allows your database to be the single source of truth for the existence of roles and what permissions they grant, and the app as the SSOT for the permissions in existence.


To answer your question more specifically:

Both of the solutions violate Single Source of Truth.

Another option is to use VARCHAR in the users table and let the application determine which roles exist. This comes with obvious other problems like overhead, potential inconsistency, and requiring a potentially big migration if you wish to make changes to existing roles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.