Django M2M relationships: Use just one intermediary table or one per entity pair?

Question

When creating many to many relationships we use an intermediary table. Lets say I use the following entities video, category, tag, and VideoCategory, VideoTag to create the relations.

I'm assuming that many tags/categories can have many videos and vice-versa.

And I do it with through keyword 'cause I want to be able to use extra fields in the future if I want.

class Category(models.Model):
    category = models.CharField(max_length=50)

    def __str__(self):
        return self.category

class Tag(models.Model):
    tag = models.CharField(max_length=50)

    def __str__(self):
        return self.tag

class Video(models.Model):
    title = models.CharField(max_length=255)
    categories = models.ManyToManyField(Category, through='VideoCategory')
    tags = models.ManyToManyField(Tag, through='VideoTag')

    def __str__(self):
        return self.title

class VideoCategory(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    video = models.ForeignKey(Video, on_delete=models.CASCADE)

class VideoTag(models.Model):
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE)
    video = models.ForeignKey(Video, on_delete=models.CASCADE)

But I was wondering if would be possible to create a taxonomy entity and handle the relationships with categories and tags from just one place.

class Category(models.Model):
    category = models.CharField(max_length=50)

    def __str__(self):
        return self.category

class Tag(models.Model):
    tag = models.CharField(max_length=50)

    def __str__(self):
        return self.tag

class Video(models.Model):
    title = models.CharField(max_length=255)
    categories = models.ManyToManyField(Category, through='Taxonomy')
    tags = models.ManyToManyField(Tag, through='Taxonomy')

    def __str__(self):
        return self.title

class Taxonomy(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE, null=True)
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE, null=True)
    video = models.ForeignKey(Video, on_delete=models.CASCADE)

Now the taxonomy entity would hold the category and tag related to videos and vice-versa.

I've included 'null=True' to be able to create relations with categories without tags and with tags but without categories.

If I don't use it. I receive an error:

# sqlite3.IntegrityError: NOT NULL constraint failed: MyApp_taxonomy.category_id

This also means that using that single taxonomy entity for the two relationships could have many NULL Fields if one of these category or tag fields are empty on every concrete relation instance (row).

Question:

What would be better ? To keep the intermediary tables separate (VideoCategory & VideoTag) Or to join these intermediary tables into just one ? (Taxonomy)

Due to my lack of experience with databases I couldn't say if I'm missing something important. If doing it with just one intermediary table would give problems in near future or something like that... Of if it is just fine.

I guess that my second example is producing ambiguous related fields. This way django cannot know which field should it use right ? This is why I should use through_fields attribute, to specify them. — Jeflopo, Mar 24 '18 at 14:22

albar · Accepted Answer · 2018-03-24T17:07:26.760

0

You have to use through_fields argument (doc):

class Video(models.Model):
    title = models.CharField(max_length=255)
    categories = models.ManyToManyField(Category, through='Taxonomy', through_fields=('video', 'category'))
    tags = models.ManyToManyField(Tag, through='Taxonomy', through_fields=('video', 'tag'))

edited Mar 24 '18 at 17:07

answered Mar 24 '18 at 11:50

albar

3,020
1
14
27

If I understand it correctly. These two lines would sit inside the `Video` entity. Then in the attribute `through_fields=('video', 'category')` these two fields would refer to the `video` and `category` fields in the `Taxonomy` entity with the `models.ForeignKey(x)` being `x` the entity `Video` or `Category` That's right ? – Jeflopo Mar 24 '18 at 13:44
May anyone edit and add an example to show how an insert should be done using these relations ? – Jeflopo Mar 24 '18 at 13:51
Take a look at the documentation: https://docs.djangoproject.com/en/1.11/ref/models/fields/#manytomany-arguments – albar Mar 24 '18 at 17:03
I've made a follow up question based on this: https://stackoverflow.com/questions/49573288/how-to-insert-using-m2m-relationship-and-intermediary-table-and-through-fields – Jeflopo Mar 30 '18 at 11:16

Django M2M relationships: Use just one intermediary table or one per entity pair?

Question:

1 Answers1

Linked