Relationships - Private Messaging

Can you explain what the user_id in each table is? I assume one is sender and one is recipient ? or?

And what is the reason for 3 tables?

message
id | sender_id | recipient_id | subject | message | read_at

The reason for three tables.

I think the message table is the conversation. A conversation has multiple messages. And a conversation can contain 2 or more users. If one user leaves (deletes) the conversation. The other users must still have access to the conversation.

Perhaps im thinking to difficult?

@nrginternetdiensten When it comes to messages, one thing I usually do is set it up in 3 main tables: [threads, participants, messages]. Threads are stand alone, participants belong to an owner and thread, and messages belong to thread and owner. Then I just use participants as a pivot to load threads for a given user. (I do prefer morphs since I use messages between different models). My participants has the last_read timestamp for managing unread counts. I also like to give my threads types, that way I can do private, group and other misc types. I like to use soft deletes, especially on participants. That way, if someone leaves a group thread but is re-added, we can show them new unread messages since after they left. Here are how I setup my base tables, and base relation I put in a trait to any model I want to message for loading threads:

Database

        Schema::create('threads', function (Blueprint $table) {
            $table->uuid('id');
            $table->integer('type')->default(1);
            $table->string('subject')->nullable();
            $table->string('image')->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->primary('id');
        });

        Schema::create('messages', function (Blueprint $table) {
            $table->uuid('id');
            $table->uuid('thread_id');
            $table->uuidMorphs('owner');
            $table->text('body');
            $table->integer('type');
            $table->timestamps();
            $table->softDeletes();
            $table->primary('id');
            $table->index('type');
            $table->foreign('thread_id')
                ->references('id')
                ->on('threads')
                ->onDelete('cascade')
                ->onUpdate('cascade');
        });

        Schema::create('participants', function (Blueprint $table) {
            $table->uuid('id');
            $table->uuid('thread_id');
            $table->uuidMorphs('owner');
            $table->timestamp('last_read')->nullable()->default(null);
            $table->timestamps();
            $table->softDeletes();
            $table->primary('id');
            $table->foreign('thread_id')
                ->references('id')
                ->on('threads')
                ->onDelete('cascade')
                ->onUpdate('cascade');
        });

Load threads relation

    public function threads()
    {
        return $this->belongsToMany(
            Thread::class,
            'participants',
            'owner_id',
            'thread_id'
        )->whereNull('participants.deleted_at')
        ->where('owner_type', get_class($this));
    }

Relations for everything else is easy. Threads have many participants/messages. Participant belong to thread and owner morph, messages belong to thread and owner morph, etc