Conversation, Many-to-Many relationship

Question

I am developing an application where I need users to interact with each other using a chat-like system.

To this purpose, I want to create a Conversation model. As far as I've been able to read, I am going to use a Many-to-Many relationship.

Having the following models: Conversation, User and Message, I imagined the following tables:

conversations: id | user1_id | user2_id - I am not sure if Laravel would understand the user IDs being numbered

messages: id | message | conversation_id | user_id

Would this be the right way to do it? And will it work with the user1_id and user2_id tables?

Answer 1

I would suggest:

• Many-to-many: User <-> Conversation. A User can have many Conversations, a Conversation consists of two or more Users
• One-to-many: User <-> Message. A Message belongs to one User. A User has many Messages
• One-to-many: Message <-> Conversation. A Message belongs to one Conversation. A Conversation has many Messages.

This will give you following SQL structure (only attributes interesting for the relationships listed):

users: id
messages: id | user_id | conversation_id
conversations: id
conversations_users: conversation_id | user_id

Think about conversations like chat rooms, its basically one shared collection of messages between a couple of users.

In Laravel, possible Eloquent models would look like this:

class User extends Eloquent
{
    public function conversations()
    {
        return $this->belongsToMany('Conversation');
    }

    public function messages()
    {
        return $this->hasMany('Message'); // not as relevant, because these are all messages across conversations
    }
}

class Message extends Eloquent
{
    public function user()
    {
        return $this->belongsTo('User');
    }

    public function conversation()
    {
        return $this->belongsTo('Conversation');
    }
}

class Conversation extends Eloquent
{
    public function messages()
    {
        return $this->hasMany('Message');
    }

    public function users()
    {
        return $this->belongsToMany('User');
    }
}

// Users conversations
$conversations = User::find(1)->conversations;

foreach($conversations as $conversation)
{
    // All members of conversation
    $members = $conversation->users;

    // All messages of conversation
    $messages = $conversation->messages;
}

Answer 2

Your Message table should look like this:

MessageID         PK
ConversationID    FK
UserID            FK
ReplyToMessageID  FK       <--- Track responses
Message           String

User1ID and User2ID is an anti-pattern. What if three users are involved in the conversation? Or four?

Having your relationships in the Message table preserves your many-to-many information, but keeps relationships between conversations and messages, and users and messages, one to many.

You don't need two user tables, just one.