5

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.

3 Answers 3

4

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.

3
  • I'd say the enum is as human readable as it would need to be for anyone technical enough to be reading raw sql output. If the main purpose of the description field is to be human readable, it's only so if you speak the language it's written in. Presentation of this type should probably be left to some i18n at a higher level.
    – Chad M
    Mar 9, 2023 at 17:45
  • How do you think i18n systems work? With tables much like this. Also, if you code the enum in a given [natural] language, would you /change/ them all when you ship the application to somewhere whose language is based on cyrillic or katakana characters? That might give you a few headaches! Installing a new "language pack" - new lookup table entries in a different language - is /somewhat/ easier.
    – Phill W.
    Mar 13, 2023 at 9:18
  • My point is that the description column is situational and so shouldn't be considered a big advantage. That said, I do agree that it can make things easier to read. I also agree that both the database and code will need to hold a definition of the types somehow. On the note of the enum, it's symbolic and only need be understood in technical role so wouldn't be changed for the scenario you outlined. Discussing how i18n is implemented seems to be getting far afield from the original question, though.
    – Chad M
    Mar 19, 2023 at 14:51
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, 2021 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, 2021 at 12:55
0

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

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 9, 2023 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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