CODEWITHSUNDEEP
pythondjangodatabase
user5359531
user5359531

Reputation: 3555

Using a single ManyToMany relation table instead of ManyToMany & ForeignKey field on multiple models?

I have a Django application that handles data analysis workflows, with database models that look something like this:

class Workflow(models.Model):
    execution_id = models.UUIDField()

class WorkflowItem(models.Model):
    workflow = models.ForeignKey(Workflow)
    type = models.CharField(choices=["input", "output"])
    files = models.ManyToManyField(File)

class File(models.Model):
    path = models.CharField()

class FileMetadata(models.Model):
    metadata = models.JSONField()
    file = models.ForeignKey(File)
    version = models.IntegerField()

A given Workflow will have many WorkflowItem's, which correspond to File's which can be used by WorkflowItem's across many Workflow's. Each File can have many associated FileMetadata's, of which the entry with the max version value is typically used for a given operation.

As the application has been growing, its getting tedious to build out all the different combinations of logic needed to find the entries in one table based on a given entry in another table just by using each tables' Foreign Key interface (Workflow <-> WorkflowItem <-> File <-> FileMetadata).

I am considering just building a table that holds all the foreign keys for every relationship in a single place. Something like this:

class WorkflowFile(models.Model):
    workflow = models.ForeignKey(Workflow)
    workflow_item = models.ForeignKey(WorkflowItem)
    file = models.ForeignKey(File)
    file_metadata = models.ForeignKey(FileMetadata)

However, I am not sure if this is a good idea or not. Its not clear to me if implementing a table like this is advantageous compared to just following all the foreign key relationships individually per-table. Its also not clear to me how I should set up such a table through Django, and if the new requirement for manually entering values into this table all the time would outweigh the reduced need for unique query logic every time I want to query these relationships. My end-goal is to provide a simpler, more consistent way to get all of the items in the relationship based on any of the other items in the relationship.

This question seems similar in premise, but I am not clear that the problem or proposed solution is relevant to what I am looking for here.

Upvotes: 1

Views: 31

Answers (1)

Charnel
Charnel

Reputation: 4432

Not sure this will actually answer your question but if you want to go the way with multiple FK's then you may consider using through table in combination with m2m changed signal to add proper FK's to this model after adding M2M records to WorkflowItem.

It'll be something like:

from django.db.models.signals import m2m_changed

class WorkflowItem(models.Model):
    workflow = models.ForeignKey(Workflow)
    type = models.CharField(choices=["input", "output"])
    files = models.ManyToManyField(File, through=IntermediateTable)

class IntermediateTable(models.Model):
    file = models.ForeignKey(File, related_name='file')
    workflow_item = models.ForeignKey(WorkflowItem, related_name='worflowitem')
    workflow = models.ForeignKey(Workflow, null=True)
    file_metadata = models.ForeignKey(FileMetadata)

def workflow_item_changed(sender, **kwargs):
    sender.workflow = sender.workflow_item.workflow
    ...
    sender.save()

m2m_changed.connect(workflow_item_changed, sender=WorkflowItem.files.through)

Upvotes: 1

Related Questions