Zystkmt

I am trying to write a django app with a PostgreSQL database. My problem can be reduced to the following minimal example:

Organization

• has 0 ... N workers

Worker

• belongs to an organization

Task

• belongs to an organization


• is assigned to no-one, or assigned to one worker within the same organization

I am unsure of how to design the database in a way that prevents inconsistent state - since both Worker and Task need to have a foreign key pointing to the Organization they belong to, it is possible to end up with an inconsistent state where a worker from organization A is assigned a task from organization B.

The only solution I could think of was verifying the constraint in the save method:

class Organization(models.Model):

    name = models.CharField(max_length=255)





class Worker(models.Model):

    name = models.CharField(max_length=255)

    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)





class Task(models.Model):

    name = models.CharField(max_length=255)

    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)

    assigned_to = models.ForeignKey(Worker, null=True, on_delete=models.SET_NULL)



    def save(self, *args, **kwargs):

        if self.assigned_to and self.assigned_to.organization != self.organization:

            raise ValidationError('Task and Worker belong in different Organizations.')

        return super(Task, self).save(*args, **kwargs

Is it possible to create a better solution which still prevents inconsistent state? My own solution doesn't seem like a good one from a database design point of view.