用于存储人与人之间聊天信息的数据库设计[英] Database Design for storing Chat Messages between people

本文是小编为大家收集整理的关于用于存储人与人之间聊天信息的数据库设计的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在尝试构建消息传递/聊天系统.可以按时间顺序存储两个人之间的对话.另外,如果用户a删除对话,则用户b仍然应该访问对话,直到他希望删除它们为止.

  1. inbox-用户从各种用户收到的所有消息将显示来自该特定线程的最新消息.

  2. 对话屏幕 - 用户A和用户B

  3. 之间对话的时间顺序

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

  1. id
  2. to_id
  3. from_id
  4. 消息
  5. 时间戳
  6. 阅读

推荐答案

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

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

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

其他推荐答案

起初,我认为当一个人删除它时,您可以将其转向或从null转到null,但这会让您失去谁发送了消息或发送给谁的消息.

您应该只添加一个deleted_by字段,该字段将包含删除它或将为null的人的ID.因此,从收件箱中选择记录时,您会有类似的内容:

从消息中选择 * to_id = myid and deleted_by <> myid

删除消息时,请检查您是否deleted_by是null,如果是null,则使用myid更新deleted_by字段,如果不是(也意味着另一方也将其删除)删除记录.

如果要具有相同的线程功能,而不是消息(即一次管理对话而不是一条消息),则应拥有另一个表(Messagethreads),其中您拥有from_id,to_id fields,deleted_by,以及thread_id字段.在消息表中,您将from_id to_id and deleted_by带有thread_id.

其他推荐答案

将有两个表. 节点 node_user

在节点表中,

  • node_id
  • 标题
  • 消息
  • 时间戳

在node_user表中,

  • node_user_id(pk)
  • node_id
  • parent_node_id(用于螺纹)
  • from_id
  • to_id
  • 时间戳
  • 阅读

用户A向用户B发送消息时,首先将消息存储在节点表中.然后,在node_user表中添加两个记录.用户删除消息时,仅在node_user表中删除第一个记录. 当用户b删除消息时,您可以从节点和node_user表中删除记录.

线程消息,

  • 使用parent_node_id

本文地址:https://www.itbaoku.cn/post/597717.html

问题描述

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.

  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

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

推荐答案

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.

其他推荐答案

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.

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:

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.

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.

其他推荐答案

There will be two tables. nodes node_user

In nodes table,

  • node_id
  • title
  • message
  • timestamp

In node_user table,

  • node_user_id(PK)
  • node_id
  • parent_node_id(for threaded)
  • from_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.

Threaded Message,

  • Use parent_node_id