1

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?

2
  • You have the project name in a project_id column. Oct 21 '20 at 5:30
  • I have created a User_Role many to many table Does the user with a role "admin" has this role for ANY project? including those which will be inserted in future?
    – Akina
    Oct 21 '20 at 5:32
0

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

0

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 */ );
2
  • I think your solution is for scenario2. If each project has the same role, as I mentioned in scenario1, can I get rid of Roles_in_Projects table, and only have Users_to_Roles_in_Projects table and has column role_id as FK of Roles, project_id as FK of Projects and user_id as FK of User_id and set a unique key for (role_id, project_id, user_id)?
    – Moon
    Oct 21 '20 at 5:56
  • @Moon If each project has any role from the roles list then you do not need in 2-step join. One joining table Users_to_Roles_to_Projects, with 3 FKs referenced to all 3 tables.
    – Akina
    Oct 21 '20 at 19:40
0
-- 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.