MySQL 用于存储人与人之间聊天消息的数据库设计

Database Design for storing Chat Messages between people

提问by Harsha M V

I am trying to build a messaging/chat system. which can store conversation between two people in a chronological order. Also if User A deletes the conversation User B still should have access the conversation until he wishes to delete them.

我正在尝试构建消息/聊天系统。它可以按时间顺序存储两个人之间的对话。此外，如果用户 A 删除对话，用户 B 仍应有权访问该对话，直到他希望删除它们为止。

1. Inbox - All the messages recieved by the user from various users will be displayed with the latest message from that particular thread.

2. Conversation Screen - Chronological order of the conversation between the User A and User B

1. 收件箱 - 用户从不同用户收到的所有消息都将与来自该特定线程的最新消息一起显示。

2. 对话屏幕 - 用户 A 和用户 B 之间对话的时间顺序

This is the basic structure of the database i have come up with. Should i store the messages twice in the database ?

这是我提出的数据库的基本结构。我应该在数据库中存储两次消息吗？

1. id
2. to_id
3. from_id
4. message
5. timestamp
6. read

1. ID
2. to_id
3. from_id
4. 信息
5. 时间戳
6. 读

采纳答案by Tim Joyce

I would use a lookup table for the messages that would store who has the rights to view that message

我将使用查找表来存储谁有权查看该消息

table->message                   |    table->messageUsers
id->0, message->'hi', user_id->1      user_id->1, message_id->0
                                      user_id->2, message_id->0

That way if a user deletes their message they are actually just deleting their relationship to the message not the message itself. you just remove them from the messageUsers table. or set a active field to 1 or 0.

这样，如果用户删除他们的消息，他们实际上只是删除了他们与消息的关系，而不是消息本身。您只需从 messageUsers 表中删除它们。或将活动字段设置为 1 或 0。

回答by John

At first I thought that when one person deleted it you could just turn either To or From to null but that would make you lose who sent the message or to whom it was addressed.

起初我认为当一个人删除它时，您可以将 To 或 From 设为 null，但这会让您失去发送消息的人或收件人。

You should just add a field deleted_by which will contain the id of the person who deleted it or will be null. So when selecting records from the inbox you have something like:

您应该只添加一个字段deleted_by，该字段将包含删除它的人的ID，或者为空。因此，当从收件箱中选择记录时，您会看到以下内容：

Select * From Messages where to_id = MyID and deleted_by <> MyID

Select * From Messages where to_id = MyID and deleted_by <> MyID

when you delete the message you check if the deleted_by is null, if it is you update the deleted_by field with MyID, if it is not (means that the other party deleted it as well) you delete the record.

删除消息时，检查deleted_by 是否为空，如果是，则使用MyID 更新deleted_by 字段，如果不是（意味着对方也删除了它），则删除记录。

If you want to have the same functionality for threads instead of messages (i.e. manage the conversation and not one message at a time) you should have another table (MessageThreads) in which you have the from_id, to_id fields, deleted_by along with a thread_id field. in the Messages table you subsitute the from_id to_id and deleted_by with the thread_id.

如果您想为线程而不是消息使用相同的功能（即管理对话而不是一次管理一条消息），您应该有另一个表（MessageThreads），其中包含 from_id、to_id 字段、deleted_by 和 thread_id 字段. 在 Messages 表中，您将 from_id to_id 和 deleted_by 替换为 thread_id。

回答by Thein Hla Maw

There will be two tables. nodes node_user

会有两张桌子。节点 node_user

In nodes table,

在节点表中，

• node_id
• title
• message
• timestamp

• 节点 ID
• 标题
• 信息
• 时间戳

In node_user table,

在 node_user 表中，

• node_user_id(PK)
• node_id
• parent_node_id(for threaded)
• from_id
• to_id
• timestamp
• read

• node_user_id(PK)
• 节点 ID
• parent_node_id（用于线程）
• from_id
• to_id
• 时间戳
• 读

When user A send a message to user B, firstly store the message in nodes table. And then, add two records in node_user table. When user A delete the message, only delete the first record in node_user table. When user B delete the message, you can delete records from both nodes and node_user table.

当用户 A 向用户 B 发送消息时，首先将消息存储在节点表中。然后，在 node_user 表中添加两条记录。当用户 A 删除消息时，只删除 node_user 表中的第一条记录。当用户 B 删除消息时，您可以删除节点和 node_user 表中的记录。

Threaded Message,

线程消息，

• Use parent_node_id

• 使用 parent_node_id