SQL - Chat database schema to delete conversation for one side or both

Question

I am designing a chat database with the next requirements:

• Only private messages, A to B. No groups nor rooms.
• When user A send message to B, if user A deletes conversation, user B still be able to view the conversation until user B deletes it.
• Messages will not be erasable individually. Only able to delete full history conversation.

And now I have this:

• When user A send message to user B, then one message register will be created, with a id. This will be the foreign key to conversation table.
• In conversation table, two registers will be created for the same message id. One for the user who sends the message, and other for the user who receives the message. Each register has a field called in-out, where specify if message was send 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's up            |
|    B    |        A       |    1   |   101  |  |   102  | Hey A, here in stackoverflow  |
|    B    |        A       |    0   |   102  |  |   103  | That's nice B, and what's new |
|    A    |        B       |    1   |   102  |  +----------------------------------------+
|    A    |        B       |    0   |   103  |
|    B    |        A       |    1   |   103  |
+--------------------------------------------+


Chat windows

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

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

*/

In this way. I am able to separate for each individual user, their full chat history, then filtering with required participant.

And separating send messages from received messages results easy just with in-out var. for example, if message is received (0) put it on the left side, or if message was sent, then put it to the right side.

SQL to get messages for user A 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's 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

To delete message history to user A, chatting with user B:

First, check if user B had not deleted their conversation. If have deleted, then, messages will be deleted from messages table. otherwise, no.

DELETE FROM conversation_table WHERE user_id=A AND participant_id=B

This will delete the full conversation between user A and B, in the User A account. User B have it's own copy of the messages.

Messages table will have meta data like:

• Timestamp (UTC current miliseconds) to get date-time and order of visualization

Well, everything is working here, but now some questions:

• Is the presented design a good model to handle thousands of users? I mean, storing for each user their incoming and outgoing messages.
• What about message id. I am thinking in a UUID with 32 chars. It is advisable? (recommended). I mean that if message only contains a body with "hello", a 32 char unique id will be required, and I think this will be unnecessary, or not?
• Can you help me to guide me in this design?

Thank you.

Answer 1

This data model should be good for your task. For Sessions should be set "Cascade Delete".

DELETE FROM Sessions WHERE DeleteFrom=1 AND DeleteTo=1

It will delete all sessions and messages deleted by both users. When a user deletes session SET DeleteFrom/To = 1.

Id is IDENTITY = Autoincrement. Order By Id provides order for messages.

Add "From BIT" = 1 if message from UserIdFrom, = 0 if - from UserIdTo.

Answer 2

Considering you are using mysql sequential autoincrement primary keys for ids

Create a column deleted by 1 and deleted by 2 (deleted by one being user 1 which has a lower user id number and delted 2 the higher number).

Then when fetching messages only get the messages after deleted id for that user based on the users column value being the message id which was last deleted.

You can use time stamps also for storing delete point but that creates problems with time zones, and different frameworks like mysql, django, laravel or express having their own date time issues.