A
A
Anton R.2019-07-30 12:07:17
MySQL
Anton R., 2019-07-30 12:07:17

The architecture of the database tables for the functionality of Private Messages, so logical?

Hello. I decided to make the functionality of private messages between users for my bulletin board training project and I see that, in principle, only two tables are needed for implementation:
Table 1. - Chat (here is a list of started dialogs)
Columns:
id - standard ID
abonent_1 - identifier of the first chat participant
abonent_2 - identifier of the second chat participant
Table 2. - Messages (this contains a list of messages that are then distributed among the dialogs)
Columns:
id - standard ID
chat_id - identifier of the dialog to which the message belongs
create_time- time when sent, for sorting
author_id - sender ID
recepient_id - recipient ID
message - message body itself, just text to start
As a result, if User1 sends a message to User2 then:
1. A dialog entity is created in the Chat table containing id and IDs of User1 and User2
2. The entity of the first message is created in the Messages table, containing its id, the Dialog identifier for grouping messages, and we fill in other fields, I think it’s clear as based on their names.
Why am I making a Chat entity? So that messages do not fall in a single heap and you can answer a specific user.
What do you think?
PS I understand that I am inventing bicycles, but for learning and understanding, I consider it important, and not immediately climb into ready-made frameworks

Answer the question

In order to leave comments, you need to log in

2 answer(s)
G
gvalex, 2019-07-30
@gvalex

Chat has the id of both participants in the conversation, so only the id of the sender (well, or the recipient) can be stored in Messages.

S
Stalker_RED, 2019-07-30
@Stalker_RED

Group chats not supported?
I would make tables
user: uid, name, and so on
channel: optional channel name, some settings
channel_has_user: channelid, userId (not necessarily two, more can be)
message: the message itself, authorUid, channelId

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question