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

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应该仍然可以访问会话，直到他想删除它们为止。

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

收件箱——来自不同用户的用户收到的所有消息都将显示在该特定线程的最新消息中。
Conversation Screen - Chronological order of the conversation between the User A and User B

会话屏幕——用户A和用户B之间会话的时间顺序

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

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

id
id
to_id
to_id
from_id
from_id
message
消息
timestamp
时间戳
read
读

3 个解决方案

#1

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。

#2

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.

一开始我认为，当一个人删除它时，你可以将它转换为null，也可以从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，否则将为null。因此，当从收件箱中选择记录时，您会有如下内容:

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

从to_id = MyID和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是否为null，如果是用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字段。在消息表中，您将from_id to_id和deleted_by与thread_id关联。

#3

There will be two tables. nodes node_user

将有两张桌子。节点node_user

In nodes table,

在节点表中,

node_id
node_id
title
标题
message
消息
timestamp
时间戳

In node_user table,

在node_user表中,

node_user_id(PK)
node_user_id(PK)
node_id
node_id
parent_node_id(for threaded)
parent_node_id(螺纹)
from_id
from_id
to_id
to_id
timestamp
时间戳
read
读

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

#1