1

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.

0
4

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..

4
  • I have a difficulty with this solution. Part of the question was: "users can be associated with one role at a time". Subsequently, in a comment, there is this remark: "A USER can only get PERMISSION by being assigned a ROLE with those PERMISSIONS". I cannot see a constraint that enforces this. Would it be possible for you to add this?
    – stefan
    Sep 10 '17 at 11:21
  • A user is in a group - it is the group which is assigned a role and subsequent permissions. Think HR - new employee gets assigned group hr-clerical which gives access to name, address, maybe salary but not evalutations. It's not as flexible as being able to give individual users an individual set of permissions ("Hey Mary, Jimmy's sick today, can you run report(X)?" -reply- "No, I don't have the necessary permissions - I have to be promoted before I can do that!". A better solution might be to assign default permissions to a user and let this be modified as needed. Requires a user_role table.
    – Vérace
    Sep 10 '17 at 11:40
  • @stefan, I think it will be tricky to implement in a "relational" fashion since it is an indirectly assigned via groups. One could use a trigger to check this, but I find the requirement a bit counter intuitive. A consequence is that a GROUP can have only one role at a time and that a USER can't be part of more than one GROUP.
    – Lennart
    Sep 11 '17 at 5:33
  • @Verace, if user and group are "sub" types of something, say ORGANISATION_UNITS, ROLES could be assigned there. Given such model, both GROUPS and USERS can have ROLES.
    – Lennart
    Sep 11 '17 at 5:38

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.