CODEWITHSUNDEEP
mysqldjangodatabasedjango-models
elethan
elethan

Reputation: 16993

Django database models - messages and recipients: many-to-many or many-to-one?

I am using MySQL for my database and have the following Message model in my Django app:

class Message(models.Model):
    sender = models.ForeignKey(User, on_delete=models.CASCADE, null=True, related_name='sender_notification')
    recipient = models.ForeignKey(User, on_delete=models.CASCADE, related_name='recipient_notification')
    message = models.TextField()
    read = models.BooleanField(default=False)
    recieved_date = models.DateTimeField(auto_now_add=True)

If I am not mistaken, this way the relationship between recipient/User and Message is one-to-many, since a single Message record can only have one recipient, but a User might have many records in the Message table. However, there will be situations where I want to send the same Message to multiple Users. With my current schema, I can just add multiple Message records, one for each recipient, with the same message text. However, this seems like a lot of duplication. In this case, would my relationship make more sense as a many-to-many one?

Initially I thought the duplication (multiple Messages with the same message field but different recipient fields) was a bad idea, but the more I think about it, it seems like modeling the many-to-many relationship would actually be more difficult, and require more data. Each Message record has a read field to indicate whether the recipient has read the message or not. If I have a single Message record with multiple recipients then it seems like I will need another table to keep track of the recipients who have and have not read the message. This means that if I want to send a message to every user, I will need to add one row to the Message table, but will have to add one row to a "read record" table for each recipient. So not only am I creating more rows total (1 Message, and one read record each for n users, as opposed to n Messages for n users), I am also adding an additional table to the mix.

I have done many searches trying to determine whether it is better to have a single table with many rows, or multiple tables with fewer rows, and it seems like there is no definitive answer; instead, it depends on your data and requirements. I am just looking for some insight into the best direction to go in my particular case, but am also interested in any general rules that I might be able to follow in the future, if such rules exist for this kind of situation.

I am very open to having more work for myself up front in order to do things the "right" way (if there is such a thing in this case), if that happens to be necessary. Thank you!

Upvotes: 0

Views: 279

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 599630

A way of solving the issue with the "read" flag for multiple users is to use a many-to-many with a custom through model, and put that flag there:

class UserMessage(models.Model):
    read = models.BooleanField()
    user = models.ForeignKey(User...)
    message = models.ForeignKey(Message...)

class Message(models.Model):
    user = models.ManyToManyField(User, through=UserMessage)

Now each user has an individual "read" field for each message, no matter how many users also read that message. Given the user and the message, you can get the intermediate model with UserMessage.objects.get(user=user, message=message).

Upvotes: 3

Related Questions