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:
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".