Database table design for many to many

Question

I have three tables: User, Role and Project

User Table

User_ID|Email             |Name     |
-------|------------------|---------|
      1|[email protected] |Bob      |
      2|[email protected]|Mary     |
      3|[email protected] |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?

Answer 1

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

Answer 2

I think that User_to_Role table is wrong and must be removed.

Create a table Roles_in_Projects. In this table you create a row for each role which may be assigned for a project (for example, each project must have an admin, but only some projects will have consultant role).

CREATE TABLE Roles_in_Projects (
    id PK,
    Role FK Roles (id),
    Project FK Projects (id) );

And create a table Users_to_Roles_in_Projects. In this table you will assign definite role in separate project to definite user (or a couple of roles - in this case you will create a couple of rows for this user).

CREATE TABLE Users_to_Roles_in_Projects (
    id PK,
    User FK Users (id),
    Role_in_Project FK Roles_in_Projects (id),
    Valid_till DATETIME /* NULL - endlessly */ );

Answer 3

-- User identified by USER_ID named NAME has email EMAIL.
--
user {USER_ID, EMAIL, NAME}
  PK {USER_ID}


-- Project role identified by ROLE_ID is named ROLE_NAME.
role {ROLE_ID, ROLE_NAME}
  PK {ROLE_ID}
  AK {ROLE_NAME}

-- sample data
(ROLE_ID, ROLE_NAME)
------------------------
(1, project_admin)
(2, project_manager)
(3, project_leader)
(4, project_participant)

Scenario 1

1. Each project has exactly one project administrator;
   for each project administrator, that administrator may administrate more than one project.

2. Each project has exactly one project manager;
   for each project manager, that manager may manage more than one project.

3. Each project has exactly one project leader;
   for each project leader, that leader may lead more than one project.

4. Each user may participate in more than one project;
   for each project, more than one user may participate in that project.

-- Project PRO_ID is administered by user PRO_ADMIN
-- managed by user PRO_MNGR, lead by userPRO_LEAD.
--
project {PRO_ID, PRO_ADMIN, PRO_MNGR, PRO_LEAD}
     PK {PRO_ID}

    FK1 {PRO_ADMIN} REFERENCES user {USER_ID}
    FK2 {PRO_MNGR}  REFERENCES user {USER_ID}
    FK3 {PRO_LEAD}  REFERENCES user {USER_ID}


-- User USER_ID participates in project PRO_ID.
--
participant {USER_ID, PRO_ID}
         PK {USER_ID, PRO_ID}

    FK1 {USER_ID} REFERENCES user    {USER_ID}
    FK2 {PRO_ID}  REFERENCES project {PRO_ID}

Scenario 2

1. Each project has at most one project administrator;
   for each project administrator, that administrator may administrate more than one project.

2. Each project has at most one project manager;
   for each project manager, that manager may manage more than one project.

3. Each project has at most one project leader;
   for each project leader, that leader may lead more than one project.

4. Each user may participate in more than one project;
   for each project, more than one user may participate in that project.

-- Project PRO_ID exists,
--
project {PRO_ID}
     PK {PRO_ID}


-- User USER_ID is in leadership role ROLE_ID
-- of project PRO_ID.
--
project_leadership {PRO_ID, ROLE_ID, USER_ID}
                PK {PRO_ID, ROLE_ID}

    FK1 {USER_ID} REFERENCES user    {USER_ID}
    FK2 {PRO_ID}  REFERENCES project {PRO_ID}
    FK3 {ROLE_ID} REFERENCES role    {ROLE_ID}

    CHECK (ROLE_ID IN (1,2,3)) -- leadership roles


-- User USER_ID participates in project PRO_ID.
--
participant {USER_ID, PRO_ID}
         PK {USER_ID, PRO_ID}

    FK1 {USER_ID} REFERENCES user    {USER_ID}
    FK2 {PRO_ID}  REFERENCES project {PRO_ID}

---

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
FK = Foreign Key