Database Role based access control design for survey app

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.

enter image description here


I create a new user_role_in_survey table to store manager and leader role and this table replaces old user_role table. Survey_ID is the FK of Survey table and participant table for participants.

enter image description here