SQL Database Schema - Chat to delete a conversation for one or both parties

Question

I am creating a chat database with the following requirements:

• Private messages only, from A to B. No groups or rooms.
• When user A sends message B, if user A deletes the conversation, user B can still view the conversation until user B deletes it.
• Messages will not be erased individually. Ability to delete the full history dialog.

And now I have this:

• When user A sends a message to user B, then a single message register with an identifier is created. This will be the foreign key for the conversation table.
• Two registers will be created in the conversation table for the same message identifier. One for the user who sends the message, and another for the user who receives the message. Each register has a field called in-out that indicates whether a message was sent or received. Example:

/*
conversation_table                              messages_table
+--------------------------------------------+  +----------------------------------------+
| user_id | participant_id | in-out | msg_id |  | msg_id |            body               |
+--------------------------------------------+  +----------------------------------------+
|    A    |        B       |    0   |   101  |  |   101  | Hello B, what up            |
|    B    |        A       |    1   |   101  |  |   102  | Hey A, here in stackoverflow  |
|    B    |        A       |    0   |   102  |  |   103  | That nice B, and what new |
|    A    |        B       |    1   |   102  |  +----------------------------------------+
|    A    |        B       |    0   |   103  |
|    B    |        A       |    1   |   103  |
+--------------------------------------------+


Chat windows

+-----------------------------------------+
| User A                                  |
+-----------------------------------------+
| Sent: Hello B, what up                |
| Received: Hey A, here in stackoverflow  |
| Sent: That nice B, and what new     |
+-----------------------------------------+

+-----------------------------------------+
| User B                                  |
+-----------------------------------------+
| Received: Hello B, what up            |
| Sent: Hey A, here in stackoverflow      |
| Received: That nice B, and what new |
+-----------------------------------------+

*/

Run code

Thus. I can separate for each individual user their full chat history, and then filter with the right participant.

And to separate sending messages from received messages is easy, just using in-out var. for example, if a message is received (0), put it on the left side or send a message and then place it on the right side.

SQL to receive messages for the user. Chatting with user B:

SELECT * FROM conversation_table C INNER JOIN messages_table M ON (C.msg_id=M.msg_id)   WHERE C.user_id=A AND C.participant=B

And to insert messages from user A to user B:

INSERT INTO messages_table (msg_id, body) VALUES (101, 'Hello B, what up')

INSERT INTO conversation_table (user_id, participant_id, in-out, msg_id) VALUES 
(A, B, 0, 101) #messages get out from user A to User B
(B, A, 1, 101) #message comes in to user B from user A

A, B:

, B . , . .

DELETE FROM conversation_table WHERE user_id=A AND participant_id=B

A B A. B .

, :

• (UTC current miliseconds),

, , :

• ? , .
• . UUID 32 . ? (). , "", 32 char, , ?
• ?

.

Answer 1

, .