I’m creating an application that has a similar roles and permissions system to Discord.
- 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:
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.
USERis a member of 1 or more
GROUPcan have 1 or more
ROLEcan have 1 or more
USERcan only get
PERMISSIONby being assigned a
GROUPwill share common permissions based on what features and content types the application has made available
ROLEScan share the same
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..