CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
James Jeffery
James Jeffery

Reputation: 12599

Am I doing something wrong in my schema?

I've designed a database schema that allows for a Facebook style messaging system (without the feature of being able to edit messages). Here it is:

enter image description here

My idea was users send messages to a conversation, and not to another user. This way, users subscribe to a conversation, allowing for multiple user style chat.

A conversation is like a room. If user bob and user joe start talking, a conversation will be created if one doesn't exist. Whenever 'bob' and 'joe' talk their messages will always have the same conversation_id. This will allow me to return all their communication by getting all messages with their conversation_id.

Multiple users can be a part of a conversation. The conversation_users table stores the the conversation_id and the user_id, which allows me to query the table to get All users who have the conversation_id X.

The problem I'm having is I want to query: Give me the conversation_id used by Joe and Bob, so I can then use this conversation_id to return all of the messages between 'Joe' and 'Bob'.

How could I achieve this? I think I've missed something important out of my model, which is making this seem more complex than it needs to be. But I can't see what.

Upvotes: 0

Views: 91

Answers (1)

O. Jones
O. Jones

Reputation: 108686

You need the intersection of the conversation_id values used by Joe, and by Bob.

How do you get the conversation_id values used by Joe?

          SELECT cu.conversation_id
            FROM Users u
            JOIN Conversation_Users cu ON u.id = c.user_id
           WHERE username = 'Joe'

You do the same for Bob, giving you two subqueries. Validate these; make sure they work correctly.

Then you do this:

SELECT cu.conversation_id
  FROM Users u
  JOIN Conversation_Users cu ON u.id = c.user_id
 WHERE username = 'Joe'
   AND cu.conversation_id IN
       (
          SELECT cu.conversation_id
            FROM Users u
            JOIN Conversation_Users cu ON u.id = c.user_id
           WHERE username = 'Bob'
       )

That gets you the list of conversation IDs involving the two users. Edit A JOIN can also handle this logic, like so.

SELECT a.conversation_id
  FROM (SELECT cu.conversation_id
          FROM Users u
          JOIN Conversation_Users cu ON u.id = c.user_id
         WHERE username = 'Joe'
           AND cu.conversation_id
       ) a
  JOIN (  SELECT cu.conversation_id
            FROM Users u
            JOIN Conversation_Users cu ON u.id = c.user_id
           WHERE username = 'Bob'
       ) b ON a.conversation_id  = b.conversation_id

The first of those queries can be placed in a MySQL view, while the second one can't. Which one is faster? Hard to know without trying them both.

There may be more than one such conversation. If you want just one, you'll have to figure out how to select it.

You haven't left anything out of your model. It's just that the logic for this kind of intersection operation is made kind of subtle because MySQL doesn't have an INTERSECTION operator.

Upvotes: 1

Related Questions