DEV Community


Discussion on: How best do I structure my User database?

oraclesean profile image
Oracle Sean ♠️

Users are users. They have one or more roles. In a simple design you would have a user table with a column for the user's role. This gives you the flexibility to add roles and move users between roles without changing the user.

  create table users (
         user_id number not null
,        role_id number not null);

  create table roles (
         role_id number not null
... );

Your application queries the user table to get the role, then the role table to get the permissions associated with it (or better still a view to get it in a single query during initial authentication).

To more properly follow good database design convention you would move the role out of the users table entirely and have a table, user_roles, to marry users to multiple roles. It would contain the user_id and role_id and allow one-to-many relationships between a user and their roles and optionally define a Property (to clarify, this is a captial P Property for the user/role record here, not a flat or apartment) for the relationship.

users defines users
roles defines roles
user_roles says what users have what roles

Consider two buildings in the application. Mary lives in Building A and manages Building B. Her role under Building A is tenant; for Building B she's a caretaker. Assigning roles in the user_roles table gives the ability to refine her access based on a higher key in the hierarchy—property_id. Now she can query her own flat in Building A but not see others, while still managing Building B through rules like:

  create table user_roles (
,        role
,        property);

insert into user_roles ('Mary', 'Tenant', 'BuildingA');
insert into user_roles ('Mary', 'Caretaker', 'BuildingB');

(Ideally you're using appropriate natural or surrogate keys for these values and not text!)

This may seem like an overly complicated way of doing it but it's exceptionally scalable, flexible, and fast. Abstracting things in the database avoids having to redesign the database later, or compromise performance and flexibility by adding complexity to work around all of the things that were "hard coded" into the initial database design.

Forem Open with the Forem app