I am creating a project management application for my portfolio similar to Jira where Users can collaborate on projects, create tickets, leave comments on tickets, etc.. I'm setting up the entity relationships and I'm a bit confused about how to set them up when there are user roles involved. For example, each User can have many projects and each project can have many Users, so there is a many to many relationship. However, since each user needs a specific role for each project, I created a third entity called ProjectUser which has a composite key of both the User id and the Project id and contains a role property.
Users on a project can create tickets, and each ticket has an author and an assignee. This is where I get confused. Should the Author and Assignee for a Ticket be a User or a ProjectUser? Is a ProjectUsers table even the correct way to go about assigning roles to users?
I currently have the Author and Assignee as Users, and entity framework seems to like this. I created a ProjectParticipant class which is a data transfer object for Users on a project that should contain the users name, email, profile picture url, and role. However, since the User entity does not directly contain a role property, when I use automapper to map a User to a ProjectParticipant, it's not able to map the role property. Is there a better/correct way to go about setting up these relationships?
This is where I get confused. Should the Author and Assignee for a Ticket be a User or a ProjectUser?
This is not a technical modeling question. Both designs are valid, and have implications on the data changes the model allows.
What should the behavior be for a Ticket if the user who assigns it leaves the project?
Do you want to allow users who aren't on the project to be on a ticket?