that conversation_user table is basically the same as message table? maybe you could consider a 3 way pivot
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?
Please or to participate in this conversation.