RDBMS Many To Many Relationships Django

Question

I am working on a django project and have difficulty in relating my data models.

I wish to achieve the following, I have 3 models Employee, Project and ProjectRole.

I wish to implement the models in such a way that User1 will have Lead (ProjectRole) in project1 but will have a Member (ProjectRole) in project2. While User2 will have Lead (ProjectRole) in project2 but Member (ProjectRole) in project1. Maybe the following table will make it clear.

Employee         Project          ProjectRole
User1            project1         Lead
User1            project2         Member
User2            project1         Member
User2            project2         Lead

I am totally confused and wish that someone explain in simple terms how relationships are to be defined within the models.

So far, I have the following in my models.py, but I am sure it is incorrect, since it is not working as expected.

class ProjectRole(models.Model):
    rolename = models.CharField(max_length=10)

class Project(models.Model):
    projname = models.CharField(max_length=10)
    projrole = models.ManyToManyField(ProjectRole)

class Employee(models.Model):
    empname = models.CharField(max_length=10)
    projrole = models.ManyToManyField(ProjectRole)

Would love some guidance on how to achieve this seemingly complex design.

Answer 1

Use a third model:

class ProjectRole(models.Model):
    rolename = models.CharField(max_length=10)


class Project(models.Model):
    projname = models.CharField(max_length=10)


class Employee(models.Model):
    empname = models.CharField(max_length=10)


class Employment(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    project = models.ForeignKey(Project, on_delete=models.CASCADE)
    role = models.ForeignKey(ProjectRole, on_delete=models.CASCADE)

the third model will thus contains triples of employee, project and role.

If you want to easily get the Employees for a Project, by looking at the Employement model, we can span ManyToManyFields that go through the Employment model with:

class ProjectRole(models.Model):
    rolename = models.CharField(max_length=10)


class Project(models.Model):
    projname = models.CharField(max_length=10)
    employees = models.ManyToManyField(
        'Employee', through='Employment', related_name='projects'
    )
    roles = models.ManyToManyField(
        ProjectRoles, through='Employment', related_name='projects'
    )


class Employee(models.Model):
    empname = models.CharField(max_length=10)
    roles = models.ManyToManyField(
        ProjectRole, through='Employment', related_name='employees'
    )


class Employment(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    project = models.ForeignKey(Project, on_delete=models.CASCADE)
    role = models.ForeignKey(ProjectRole, on_delete=models.CASCADE)

but if you need the full details, you can just use my_project.employment_set.all() to get the Employment records that thus contain also the details regarding the .role for that .employee.