Mysql – Database table design for many to many

database-designMySQL

I have three tables: User, Role and Project

User Table

User_ID|Email             |Name     |
-------|------------------|---------|
      1|bobtest@gmail.com |Bob      |
      2|marytest@gmail.com|Mary     |
      3|samtest@gmail.com |Sam      |

Role Table

User_Role_ID|Role_Name          |
------------|-------------------|
           1|project_admin      |
           2|project_manager    |
           3|project_leader     |
           4|project_participant|

Project Table

Project_Id|Project_Name|
----------|------------|
         1|    Project1| 
         2|    Project2|
         3|    Project3|

I have created a User_Role many to many table:

User_Role Table

User_Id|User_Role_ID| 
-------|------------|
      1|           1|
      1|           3|
      2|           3|
      2|           1|
      1|           2|
      3|           3|

How can I design the database to show the relationship between user, role and project with the below scenario? One participant can be assigned to multiple projects.

Scenario1: If each project must have all the same roles, one project_admin, one project_manager, one project_leader and multiple project_participant, how should I design database?

What I though is to create a Project table as:

Project_Id|Project_Name|Project_admin|Project_manager|Project_leader|
----------|------------|-------------|---------------|--------------|
         1|    Project1|            1|              2|             1|
         2|    Project2|            2|              3|             3|
         3|    Project3|            3|              4|             1|

All Project_admin, Project_manager and Project_leader will be the foreign key of User table and one participant table for the project and those users with participant role, participant_id will be the PK and Project_ID and User_ID are the FK of project table and user table.

Participant_Id|Project_Id|User_Id|
--------------|----------|-------|
             1|  Project1|      1|
             2|  Project1|      2|
             3|  Project1|      3|
             4|  Project2|      2|
             5|  Project3|      3|
             6|  Project3|      4|

I'm not sure if this is fine as if I have a new role, I need to add a new column on the project table.

Scenario2: If each project can have different roles, for example, project1 can have one project_admin and many project_participant, project2 has one project_admin, one project_leader and many project_participant and project3 has one project_admin, one project_manager and many project_participant ,how should I design database?

Best Answer

Drop Project_admin|Project_manager|Project_leader from the project table, and add a role_id column to the project_participants table.

Related Question