Sql-server – How to design a table with relationships to a group of users or individual users

database-designsql server

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.

Best Answer

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
Related Question