Let there are 3 types of Admin,

1. Service Admin
2. Project Admin
3. ProjectTeam Admin

And tables are like below,

CREATE TABLE UserInfo {
  userid BIGINT PRIMARY KEY,
  ...
}
CREATE TABLE ProjectInfo {
  projectid BIGINT PRIMARY KEY,
  ...
}
CREATE TABLE ProjectTeamInfo {
  teamid BIGINT PRIMARY KEY,
  projectid BIGINT NOT NULL,
  CONSTRAINMT `rfk_team_to_project`
    FOREIGN KEY `projectid` REFERENCES TO ProjectInfo(projectid)
    ON DELETE CASCADE ON UPDATE CASACDE,
  ...
}

And if I want to grant specific administrator permission of User Management, Project Management, ProjectTeam Management to specific user, what would be a best schema for defining AdminInfo table?

Guess that there are tables like below,

CREATE TABLE AdminInfo {
  userid BIGINT NOT NULL,
  isServiceAdmin BOOLEAN NOT NULL,
  isProjectAdmin BOOLEAN NOT NULL,
  admProjectId BIGINT DEFAULT NULL, (ref to projectinfo table)
  isTeamAdmin BOOLEAN NOT NULL,
  teamId BIGINT DEFAULT NULL, (ref to teaminfo table)
}

There are 3 possible cases,

1. Service Admin(User Management) : { userid: 1, isServiceAdmin: true }
2. Project Admin : { userid: 2, isProjectAdmin: true, admProjectId: 1000 }
3. Team Admin : { userid : 3, isTeamAdmin: true, teamId: 100 }

But this looks very awful and also not what RDBMS meant to be.

To sum up, schema looks like below,

User can have vary types of AdminPreviliges depend of it's previlige category like ServiceAdmin, ProjectAdmin, TeamAdmin.

How to define tables which can vary it's information depend onf specific category?

Only way to do it so is just define ServiceAdminInfo, ProjectAdminInfo, TeamAdminInfo seperately?

CodePudding user response：

Perhaps you're looking for user membership design.

The basic design would go with this :

Users > Roles (Groups) > Privileges (Permissions)

So, you can use Roles to create roles for each usage type such as Admin, Project ..etc. and use Privileges to define each role privileges (basic can be SELECT, INSERT, DELETE)

The last thing you need is just to link them together with a one-to-many relationship. (each user can have one or more role, and each role can have one or more privileges).

From there you can extend your design to where you see it fits to your current business.

UPDATE Comments

There are several groups, and privileges are differ from each groups per user." In this case, how can I resolve it?

You link Groups, Permissions and Users with tables using their foreign keys.

Like

CREATE TABLE GroupPermissions {
  Id INT PRIMARY KEY,
  GroupId INT,
  PermissionId INT,
  CONSTRAINT `FK_GroupPermissions_GroupId` FOREIGN KEY GroupId REFERENCES TO Groups(Id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE, 
  CONSTRAINT `FK_GroupPermissions_PermissionId` FOREIGN KEY PermissionId REFERENCES TO Permissions(Id) 
}


CREATE TABLE GroupUsers {
  Id INT PRIMARY KEY,
  GroupId INT,
  UserId INT,
  CONSTRAINT `FK_GroupUsers_GroupId` FOREIGN KEY GroupId REFERENCES TO Groups(Id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE, 
  CONSTRAINT `FK_GroupUsers_PermissionId` FOREIGN KEY UserId REFERENCES TO Users(Id) 
}


CREATE TABLE GroupPermissionUsers {
  Id INT PRIMARY KEY,
  GroupPermissionId INT,
  UserId INT,
  CONSTRAINT `FK_GroupPermissions_GroupPermissionId` FOREIGN KEY GroupPermissionId REFERENCES TO GroupPermissions(Id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE, 
  CONSTRAINT `FK_GroupUsers_PermissionId` FOREIGN KEY UserId REFERENCES TO Users(Id) 
}

Groups and Permissions are tables where you store the definitions for both. While GroupPermissions and GroupUsers are tables where you store the assigned integrities. However, GroupPermissionUsers is the table where you actually check for each user to get the user groups and permissions. So, when a user tries to do something, and you need to verify his/her permission, you just use GroupPermissionUsers get the assigned permissions, and validate them against the object permissions that you assigned to.