What would a “properly designed” schema for roles and permissions look like?

I’m creating an application that has a similar roles and permissions system to Discord.

Basically

• Users are members of groups
• Groups have roles
• Roles have permissions
• And users can be associated with one role at a time.

I’m not sure how to design a system like this. Originally, I was thinking I’d have a roles table, and for each permission there would be a boolean column like: can_change_nickname.

That doesn’t seem right though…

If you have any recommended reading, or can offer an answer with details on why it’s done that way, I’d really appreciate that.

• A USER is a member of 1 or more GROUPS
• A GROUP can have 1 or more ROLES
• A ROLE can have 1 or more PERMISSIONS
• A USER can only get PERMISSION by being assigned a ROLE with those PERMISSIONS.
• Every GROUP will share common permissions based on what features and content types the application has made available
• Different ROLES can share the same PERMISSIONS.

So it looks like I’ll need a table for both ROLES and PERMISSIONS. Now I just need to find what the PERMISSIONS table would look like.

Let’s start with GROUPS. In general, I use plural for table names unless there is a collective noun that works. You may have a different preference.

CREATE TABLE GROUPS
( GROUP_ID ... NOT NULL PRIMARY KEY
, ... ) ;

Since a user can be a member of several groups (and I assume that a group can contain more than 1 user), we need a n-n relationship. This is usually implemented via a junction table:

CREATE TABLE USERS
( USER_ID ... NOT NULL PRIMARY KEY
, ... ) ;

CREATE TABLE USER_GROUPS
( USER_ID ... NOT NULL
      REFERENCES USERS (USER_ID)
, GROUP_ID ... NOT NULL
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (USER_ID, GROUP_ID)
);

Next we have PERMISSIONS:

CREATE TABLE PERMISSIONS
( PERMISSION_ID ... NOT NULL PRIMARY KEY
, ...
);

Since a permission can exists for several roles, we once again use a n-n relatyionship table:

CREATE TABLE ROLES
( ROLE_ID ... NOT NULL PRIMARY KEY
, ...);

CREATE TABLE ROLE_PERMISSIONS
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, PERMISSION_ID ... NOT NULL   
      REFERENCES PERMISSIONS (PERMISSION_ID)
,   PRIMARY KEY (ROLE_ID, PERMISSION_ID)
);

Finally we can describe the relationship between groups and roles. If I got it right that is once again an n-n relationship:

CREATE TABLE GROUP_ROLES
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, GROUP_ID ... NOT NULL   
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (ROLE_ID, GROUP_ID)
);

This is of course just a sketch. I invented attribute names blindly, if there is an attribute name that exists in reality, use that..