I’m going to design the RBAC for a survey app. Each survey has the same role as below and only 1 manager, 1 leader and multiple participants.
Role_ID|Role_Name | -------|-----------| 1|admin | 2|manager | 3|leader | 4|participant|
Each user can have multiple roles and this user can choose to be the participant role to join one survey or not when he is the manager or leader of a survey. To make the user only can action the survey he owns, for example, UserA is assigned to be the leader of SurveyA, so he can only has the capability, edit_survey, on SurveyA but not another surveyB which he’s not assigned to, how should I design the database?
I generated two options, can someone check which one is better or there is another better solution?
I put the role manager and leader column as the FK of User_ID from User table on Survey table as it’s one to one relationship and create a new participant table for participants.
I create a new
user_role_in_survey table to store manager and leader role and this table replaces old
Survey_ID is the FK of Survey table and participant table for participants.