[FIXED] SQL double many-to-many relations with only two tables

Issue

Having two tables, one of them is called Tasks and the other one Relations where every task in Tasks might have 0-to-many predecessors and similarly, 0-to-many successors that relations among tasks are stored in the Relations-table.

So, if Task_1 is predecessor of Task_2 and Task_2 is predecessor of Task_3 then I would like to store the following records in the Relation-table:

pk	predecessor	successor
0	1	2
1	2	3

for instance, using Django's model definition:

class Tasks(models.Model):
    name_text = models.CharField(max_length=200)
    duration_int = models.IntegerField(default=1)

class Relations(models.Model):
    predecessors = models.ManyToManyField(Tasks)
    successors = models.ManyToManyField(Tasks)

Unfortunately, it does not work. I believe, that it is because giving the definition for successors as another relation to Tasks what already does exist might be inappropriate; although I also believe that Relation-table shall cascade back to Task-table with both of the relation: with predecessor and with the successors too in order to ensure the integrity.

Would any of you please help me out with this problem?

Solution

The modeling makes not much sense, you use two ForeignKey fields ^[Django-doc] for the predecessor and the successor:

class Task(models.Model):
    name_text = models.CharField(max_length=200)
    duration_int = models.IntegerField(default=1)
    successors = models.ManyToManyField(
        Task,
        through='Relation',
        through_fields=('predecessor', 'sucessor'),
        related_name='predecessor',
    )


class Relation(models.Model):
    predecessor = models.ForeignKey(
        Task, on_delete=models.CASCADE, related_name='successor_relations'
    )
    successor = models.ForeignKey(
        Task, on_delete=models.CASCADE, related_name='successor_relations'
    )

Answered By - Willem Van Onsem