Django intermediate table with a many-to-many field instead foreign key?

Question

I have 2 models in my application:

User
Tower

My goal is to associate many towers to a user using an intermediate table because I need to add a period of time.

So I have something like this in my models:

class Tower(models.Model):
    name = models.CharField(max_length=128)

class User(models.Model):
    name = models.CharField(max_length=128)
    members = models.ManyToManyField('Tower', through='Dates')

class Dates(models.Model):
    user = models.ForeignKey('User', on_delete=models.CASCADE)
    tower = models.ForeignKey('Tower', on_delete=models.CASCADE)
    begin_date = models.DateTimeField()
    end_date = models.DateTimeField()

But my goal was to have the field tower in class Dates to a many-to-many like this:

tower = models.ManyToManyField('Tower', blank=True)

So that i can associate many towers to a user in a certain period. But unfortunately django says that i need to use forgeignkey to Tower and User classes.

Have any solution for this? To apply directly the many-to-many field in the intermediate table? Or I must create a new class, sort of a GroupTower, that have a many-to-many field to the Tower class? Something like this:

class Tower(models.Model):
    name = models.CharField(max_length=128)

class GroupTower(models.Model):
    tower = models.ManyToManyField('Tower', blank=True)

class User(models.Model):
    name = models.CharField(max_length=128)
    members = models.ManyToManyField('Tower', through='Dates')

class Dates(models.Model):
    user = models.ForeignKey('User', on_delete=models.CASCADE)
    tower = models.ForeignKey('GroupTower', on_delete=models.CASCADE)
    begin_date = models.DateTimeField()
    end_date = models.DateTimeField()

Thanks in advance.

score 4 · Accepted Answer · answered Mar 28 '19 at 15:31

There is so many ways that you can design the database.

An example:

class Tower(models.Model):
    name = models.CharField(max_length=128)

class User(models.Model):
    name = models.CharField(max_length=128)
    members = models.ManyToManyField('UserTower')

class UserTower(models.Model):
    user = models.ForeignKey('User', on_delete=models.CASCADE)
    tower = models.ManyToManyField('Tower')
    begin_date = models.DateTimeField()
    end_date = models.DateTimeField()

In this design you can add many UserTower instances to a user and each UserTower instance can have many towers.

Now if you query the members for a user:

User.objects.get(pk=1).members.all()

You will have members grouped by periods of time if you saved them this way but it requires you to write some codes to avoid duplicates for begin_date, begin_date and user.

And now if you need towers:

user_members = User.objects.get(pk=1).members.all().values_list('tower', flat=True)
towers = Tower.objects.filter(pk__in=user_members).distinct()

This design is okay only if you really don't want duplicates with same begin_date and begin_date which i can't find a reason for.

You can still have all the features if you add multiple instances with same begin_date, begin_date and user but different tower.

This is exactly what I was expecting. Thank you so much for your help. — Pedro Faria, Mar 28 '19 at 17:51

Django intermediate table with a many-to-many field instead foreign key?

1 Answers1