Keeping database foreign keys consistent

Question

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.

Keeping database foreign keys consistent

0 Answers