Designing a database schema for a messaging system?

Question

MySQL

Hello!
The task is to develop a messaging system as in contact. Dialogues, users, messages.
Googling and analyzing the essence of the task, I came to the following database structure:

$this->createTable('{{dlg_usr}}', [
            'dlg_id' => 'int(11) NOT NULL',
            'user_id' => 'int(11) NOT NULL',
            'created_at' => 'timestamp NOT NULL',
        ]);

        $this->createTable('{{dlg_msg}}', [
            'id' => 'pk',
            'author_id' => 'int(11) NOT NULL',
            'type' => 'int(11) NOT NULL',
            'message' => 'longtext NOT NULL',
            'created_at' => 'timestamp NOT NULL',
            'update_at' => 'timestamp NOT NULL',
        ]);

        $this->createTable('{{dlg_msg_usr}}', [
            'dlg_id' => 'int(11) NOT NULL',
            'user_id' => 'int(11) NOT NULL',
            'msg_id' => 'int(11) NOT NULL',
            'is_new' => 'int(11) NOT NULL',
            'hide_status' => 'int(11) NOT NULL',
            'moderated_status' => 'int(11) NOT NULL',
            'hide_at' => 'timestamp NOT NULL',
            'moderated_at' => 'timestamp NOT NULL',
            'created_at' => 'timestamp NOT NULL',
            'update_at' => 'timestamp NOT NULL',
        ]);

Table 1. Table of users and dialog identifiers.
Decided not to start a separate table for id dialogs. Since there is nothing more to store besides the id and date of creation. When creating a new dialog, two or more entries will be entered into this table with user ids and dialog id equal to the maximum id + 1 from this table, and the date of creation. When adding a new user to the dialog, one entry will be made respectively with the user id and the dialog id.
Table 2. Message table.
The messages themselves will be entered into this table. In addition to the text of the message, I plan to store the author's id, the type of message (system, user, etc.), and the creation and editing timestamps.
Table 3. Linking table of messages and users.
The purpose of this table is to link messages and dialogs. When creating a message in the dialog, the number of entries will be created, which corresponds to the number of users in the dialog. Also, this table allows you to implement the functionality of deleting (hiding) messages. In the event that any user deletes a message from his dialog, other users will still see this message in the dialog.
I would like to hear constructive criticism of this scheme and logic in general. I will also be glad if you point out possible problems in the future when using such a message storage scheme.
Thanks to all!

Answer 1

Similarly