0

I'm making a PHP / MySQL web app with basic messaging functionality. Two users will have the ability to have a "conversation" which will contain a series of messages.

Here are the model relationships that I conceived:

User > many-to-many > Conversation

Conversation > many-to-many > User AND one-to-many > Messages

Message > belongs-to > Conversations (it could also optionally belongs-to User)

To achieve this, I would need the following tables/fields:

Users

  • ID
  • ...
  • [lots of other fields that don't matter]
  • ...

Conversations

  • ID

ConversationsUsers

  • user_id
  • conversation_id

Messages

  • ID
  • user_id
  • conversation_id
  • text

As you can see, I'm considering a ConversationsUsers join table to manage the many-to-many relationship between users and conversations.

My questions:

I'm trying to figure out if this is the best way to do this, and two things bother me currently:

  1. It seems inefficient/awkward for the Conversations table to have only one ID field whose job is simply to bundle messages. Is there a more elegant way to do this?

  2. I need to store some user-specific data about the conversation, such as is_archived. In my plan above this is not possible, unless I add those fields to the join table. I think this is knowns as hasMany through in CakePHP, which is the framework I'm using (v2.6.x). I can't think of a way to avoid this, but I'd like to if it's possible because I've found those relationships to be difficult to maintain in the past.

Any other thoughts / feedback about my approach are welcome.

Community
  • 1
  • 1
emersonthis
  • 32,822
  • 59
  • 210
  • 375

1 Answers1

0

Hi see below staructure:

Users
    ID
    ...
    [lots of other fields that don't matter]
    ...

Messages

    ID
    unique_conversation_id (put timestamp in this field and use it like unique conversation id and do proper indexing)
    user_id
    text

You can easily remove these tow tables conversations and conversationsUsers.

keep users table as it contains lots of information and handle everything else from messages table only. Every conversation has unique conversion id which will be timestamp. Now what is happening in this messages table, whenever you create new conversation you have to put timestamp at that movement only and after that use this as conversion id for that particular conversation.

Anubhav
  • 1,605
  • 4
  • 18
  • 31
  • Thanks. But in this system, how do I track whether a conversation `is_archived` or `has_unread`? – emersonthis Jan 07 '15 at 19:15
  • Thinking about this more... I love the idea of something so minimal... but won't this also come at a high price when I need to know things like, say, who is the other user in this conversation? I'm worried that the minimalism of the DB structure will result in a lot of extra logic in the controllers. – emersonthis Jan 07 '15 at 19:18
  • Too many tables will result in deadlock but extra logic will not. Lets say you want other user then find all messages corresponding to particular conversion then manipulate that array to get all users which can be done using http://book.cakephp.org/2.0/en/core-utility-libraries/set.html#Set::classicExtract – Anubhav Jan 08 '15 at 01:29
  • Ok. But I'm still not clear on how your proposal solves my problem. Is there a way to store conversation "meta" data, such as `has_unread` etc? – emersonthis Jan 08 '15 at 01:52
  • You can have one more field in messages table has_unread which will solve your problem. If you talk about relationship it will be many-to-many. – Anubhav Jan 08 '15 at 02:01
  • Respectfully, I don't think you're understanding my question. `has_unread` might be a bad example. Consider something like `is_archived`. Either user should have the ability to "archive" a message right? But in your proposal that information would be only stored on one record. So if I send you a message/conversation. And YOU archive it, then it would be archived for me ALSO. Right? Do you see the problem I'm addressing? – emersonthis Jan 09 '15 at 01:07
  • OK put two fields in messages table has_unread_sender and has_unread_receiver, similarly with is_archived. – Anubhav Jan 10 '15 at 03:04
  • Let me give you a real world example to demonstrate my concern with this approach: Say I want to have a "Messages" page which is like an inbox showing a list of all the user's conversations, threaded so the user can see both sides of the conversation. What does that DB request look like? `SELECT * FROM messages WHERE ...`? It looks like I'd have to first request only the user's messages, extract the `conversation_id`'s and then request the other half(s) of the conversation(s) using the conversation_id. And even then, I won't find the incoming messages to which the user has not yet replied. – emersonthis Jan 10 '15 at 13:00
  • If you prefer, maybe just explain to me the last part of what I just wrote. How do a show a user when someone has sent them a new message? Don't we now at least need a `to_user_id` field or something? Or am I missing something? – emersonthis Jan 10 '15 at 13:03