Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

allw's avatar
Level 4

How to store a conversation? (many to many?)

Hi all,

So I am looking at a way to store a conversation between users in a "perfect" way, right now it is only planned to be between two users at once but I would like the option of having up to about 4 people in the conversation.

My mind is having a bit of a meltdown trying to break it down logically - so far I have this:

*A user can contribute many messages to the conversation (one to many).

*A user can be a member of many conversations (one to many).

(This results in many users to many conversations)

How to tackle this?

*Creation of a messages table which will contain

        Schema::create('messages', function (Blueprint $table) {
            $table->increments('id')->unsigned();           //message_id
            $table->integer('user_id')->unsigned()->index();    //user_id
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); //if user deletes please delete all messages from that user.
            $table->integer('conversation_id')->unsigned(); //track which conversation all these messages belong to.
        $table->foreign('conversation_id')->unsigned()->index();
            $table->integer('reply_to_message_id')->unsigned(); //order of the messages?
        $table->boolean('has_read'); //has the first "reader" read it
            $table->string('body'); //contents
            $table->timestamps(); //created_at = sent time, updated_at = time read
            $table->softDeletes(); //admin needs to be able to read
        });

*Obviously there then needs to be a pivot table to resolve these messages into a conversation

    Schema::create('conversation_users', function (Blueprint $table) {
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->unsigned()->index();
        $table->increments('conversation_id')->unsigned()->index();
        $table->timestamps(); //might be a handy thing to keep hold of like you started chatting at X time.
        $table->softDeletes(); //admin read
    });

Quite a few questions from this, i obviously need an automatic way to keep conversation_id going so would it be better to have the increments on the pivot table or in the messages table?

-Will these work correctly?

-Would this allow the conversation to be held easily?

-and finally is this reasonably well normalised?

0 likes
4 replies
lmxdev's avatar

that conversation_user table is basically the same as message table? maybe you could consider a 3 way pivot

allw's avatar
Level 4

i was under the impression the pivot table is what you used to join the two halves of data that is stored like this together?

Numline1's avatar

Aren't you overthinking this?

How about just "User m2m Conversation hasMany Messages".

You'd end up with pivot table for conversation_user (if you prefer this naming, or just users_conversations, as I prefer it). You can then attach Message to any conversation and accessing them would be just a matter of $user->conversations()->whereId(24)->messages()->get() (or something similar, this is untested).

Alternatively, you can store messages directly on the pivot, but that seems a bit messy in terms of DB structure.

1 like
marc.runkel's avatar

I always take the approach of modeling "things" to tables and then examining the links.

In this case we have:

  • Users

  • Conversations

  • Messages

  • Users -> can belong to many conversations

  • Messages -> each message belongs to a single conversation and a single user

So from that, you'd need four tables:

  • Users
  • ConversationMembers
  • Conversations
  • Messages

The content of each would be as follows:

  • Users is self evident (including a UserID)
  • Conversations would have an ID and various metadata
  • ConversationMembers would be a link of UserIDs to ConversationIDs.
  • Messages would include UserIDs and ConversationIDs and the actual message

The number of messages a user creates is really irrelevant because they are always "their" messages and that's all you really need to store.

Please or to participate in this conversation.