Whats the right table relationship for these entities? 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 &quot;only one role in each project for a user&quot;.
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.