What's the use of Intermediate models in Django (Many to Many Relationship)

#django #python #database

The Many-to-Many relationship is a cornerstone of database design, but what happens when you need to store extra data about that relationship? Standard Many-to-Many falls short.

In Django, the solution is the intermediary model or intermediary table.

Example 1: Enriching a Customer-Product Relationship

Let's start with a classic e-commerce scenario. A Customer can buy many Products, and a Product can be bought by many Customers. This is a Many-to-Many link.

If we only link Customer and Product, we can't capture vital transaction details like:

Quantity: How many units of the product were bought?
Date: When did the transaction occur?

This extra data doesn't belong to the Customer (it's not part of their intrinsic profile) or the Product (it changes with every sale). It belongs to the act of purchasing.

To capture this, we introduce a third model: Purchase. This model acts as the junction table, connecting Customer and Product while holding the relationship-specific data.

It holds:

product
customer
date purchased
quantity purchased

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=128)
    price = models.DecimalField(max_digits=5, decimal_places=2)

class Customer(models.Model):
    name = models.CharField(max_length=128)
    age = models.IntegerField()
    # Key change: Use the 'through' argument to specify the intermediary model
    products = models.ManyToManyField(Product, through='Purchase')

class Purchase(models.Model):
    # These two ForeignKey fields define the Many-to-Many relationship
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)

    # These fields store the *data about the relationship*
    date = models.DateField()
    quantity = models.IntegerField()

When you buy a product, you do it through the Purchase model: the customer buys quantity of product in date.

The magic happens with the through='Purchase' argument on the ManyToManyField. It tells Django: "Don't create a default intermediary table for this relationship; use my custom Purchase model instead."

Something important to clarify.
The necessity for a third, connecting table is an inherent requirement of the Many-to-Many (M2M) relationship in databases, but Django handles this in two ways:

for simple M2M links (like Customer to Product) where no extra data is needed, Django automatically creates this intermediate table internally
if you need to store relationship-specific information, such as the quantity purchased in an Order, you must manually define this connecting table as a custom "through" model and use the through argument in your ManyToManyField, which requires you to manage the relationship by creating records in that explicit model instead of using the automatic methods.

As the Django documentation says:

...if you want to manually specify the intermediary table, you can use
the through option to specify the Django model that represents the
intermediate table that you want to use.

Example 2: Tracking Band Membership History

Let's now use the example from the Django Documentation.

You have a database of musicians with a Many-to-Many relationship to bands: a person (musician) can belong to many Groups (bands), and a Group has many Persons.

What data do you want to keep?

For musicians (person): name and instrument they play.
For the bands: name and style.

    from django.db import models

    class Person(models.Model):
        name = models.CharField(max_length=128)
        age  =  models.IntegerField() 


    class Group(models.Model):
        name   = models.CharField(max_length=128)
        style  = models.CharField(max_length=128)
        person = models.ForeignKey(Person, on_delete=models.CASCADE)

But, wouldn't you think that knowing when the person joined the band is important?

What model would be the natural place to add a date_joined field? It makes no sense to add it to Person or Group, because it's not an intrinsic field for each of them, but it's related to an action: joining the band.

So you make a small, but important adjustment. You create an intermediate model that will connect the Person and the Group with their Membership status (which includes the date_joined field).

The new version is like this:

from django.db import models
      
class Person(models.Model):
    name = models.CharField(max_length=128)
    age = models.IntegerField() 

class Group(models.Model):
    name = models.CharField(max_length=128)
    style = models.CharField(max_length=128)
    # Define the M2M relationship using the Membership model
    members = models.ManyToManyField(Person, through='Membership')

class Membership(models.Model):
    # Foreign Keys linking to the two main models
    person = models.ForeignKey(Person, on_delete=models.CASCADE)
    group = models.ForeignKey(Group, on_delete=models.CASCADE)

    # The crucial data about the relationship itself
    date_joined = models.DateField()

The changes are:

You added a new class called Membership which reflects the membership status.
In the Group model you added members = models.ManyToManyField(Person, through='Membership'). With this you relate Person and Group with Membership, thanks to through.

Example 3: students enrolling in courses.

The Students and Courses example is a classic demonstration of a many-to-many relationship, as a student can enroll in multiple courses, and a course can have multiple students.

You'll define the relationship by placing the ManyToManyField on one of the models. Let's use the Course model, but placing it on Student would work just as well.

from django.db import models

class Student(models.Model):
    name = models.CharField(max_length=100)
    student_id = models.CharField(max_length=10, unique=True)

class Course(models.Model):
    title = models.CharField(max_length=150)
    course_code = models.CharField(max_length=10, unique=True)

    # Define the M2M relationship here:
    # A Course can have many students, and a Student can be in many courses.
    students = models.ManyToManyField(Student)

But what if we want to store the date of enrollment?
Ahhaaa.... we have to use an intermediate model.

Ok, create a new model called Enrollment that explicitly links Student and Course and includes your new field, enrollment_date.

In models.py:

from django.db import models

class Student(models.Model):
    name = models.CharField(max_length=100)
    student_id = models.CharField(max_length=10, unique=True)

class Course(models.Model):
    title = models.CharField(max_length=150)
    course_code = models.CharField(max_length=10, unique=True)
    students = models.ManyToManyField(Student, through='Enrollment')

class Enrollment(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)    
    course = models.ForeignKey(Course, on_delete=models.CASCADE) 
    enrollment_date = models.DateField() 

    class Meta:
        # Ensures that the combination of student and course is unique
        unique_together = ('student', 'course')