0

I want to store all the data for the following entities:

  • Users
  • Projects
  • Roles

A user can work in multiple projects and can have many roles, but only one role for a user should be allowed in each project, for example:

Jhon can work only as a developer in project #1 and can also work as a designer in project #2

I think the relationship should be:

Diagram

Where the table User_Project has:

  • user_id
  • project_id
  • role_id

and the user_id,project_id combination is the primary key, and the role_id field is a foreign key to the roles table, where there is a row for each:

role_id role 1 project manager 2 developer 3 designer

is that the right way to describe the relationship between the entities ??

At first i thought about a many to many relationship for each the users - projects, projects - roles and user - roles, but i dont think it describes the "only one role in each project for a user".

1
  • A user can work in multiple projects and can have many roles, but only one role for a user should be allowed in each project Too low data. "only one role in each project for a user" Maybe you sumply need unique index (user, project) in linking table, maybe you need 2 separate linking tables - for example, role-to-project and user-to-1st_linking_table... – Akina Oct 14 '20 at 5:44
0

I would simply have:

USER - ROLE - PROJECT

I presume a user cannot be part of a project without having a role? The ROLE table facilitates your many-to-many mapping between users and projects: one user can map to many projects (with each mapping requiring a role record) and vice versa: one project can map to many users.

Simply add a UNIQUE constraint on the (user_id, project_id) columns on the ROLE table to ensure the user exists only once per project in the ROLE table.

If you need to standardise a list of options to describe roles, then your second diagram suits best - which is the same as mine, with an additional table to describe roles.

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.