0

I have a feature in my application that allows setting a set of permissions for groups of users or individual users. Considering that a group contains zero or more users and a user belongs to one or more groups, how should I design my permissions table so I'm able to reference the group id or individual user ids?

A permission set can have both groups and users referenced at the same time, this way if a user is added to a group that is part of a permission set, he/she automatically gets the same permission set.

2

you could use something like this:

create table users (
    userid int not null primary key
  )

create table groups (
    groupid int not null primary key
  )

create table permissions (
    permissionid int not null primary key
  )

create table users_groups (
    userid int not null
  , groupid int not null
  , constraint pk_users_groups primary key clustered (userid, groupid)
  , constraint fk_users_groups_userid foreign key references users(userid)
  , constraint fk_users_groups_groupid foreign key references groups(groupid)
  )

create table users_permissions (
    userid int not null
  , permissionid int not null
  , constraint pk_users_permissions primary key clustered (userid, permissionid)
  , constraint fk_users_permissions_userid foreign key references users(userid)
  , constraint fk_users_permissions_permissionid foreign key references permissions(permissionid)
  )

create table groups_permissions (
    groupid int not null
  , permissionid int not null
  , constraint pk_groups_permissions primary key clustered (groupid, permissionid)
  , constraint fk_groups_permissions_groupid foreign key references groups(groupid)
  , constraint fk_groups_permissions_permissionid foreign key references permissions(permissionid)
  )

and query it with something like this:

select p.permissionid --,p.other_permissions_columns
  from users_permissions up 
    inner join persmissions p on p.permissionid = up.permissionid
  where up.userid = @userid
union 
select p.permissionid --,p.other_permissions_columns
    from users_groups ug 
      inner join groups_permissions gp on gp.groupid = ug.groupid
        and ug.userid = @userid
      inner join persmissions p on p.permissionid = gp.permissionid

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.