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.
