主页 / dba / 问题 / 192172
Accepted
Vanya Avchyan
Asked: 2017-12-02 06:00:19 +0800 CST

如何创建灵活的表模式来存储来自不同聊天的消息？

  • 772

请帮忙解决以下情况：

存储消息历史的 API 有两种，它是Zopim和Chat2Desc（导入 Postman）。虽然这两个但可以然后其他人出现。

我的数据库与users表：

Table users
id , email, phone, ...

在Zopim中，通过电子邮件识别用户，在Chat2Desc中通过电话识别用户。对我来说，这两个领域很重要，不管聊天是什么，有多少不是。

也就是说，如果我在消息中收到电子邮件或用户电话，我会向我的数据库 ( table users) 发出请求以识别我的用户。

原则上，即使聊天室的结构也不重要。我会以某种方式选择它们。这里是如何正确保存它们的方法，以至于我为每个人准备了一个结构。

这就是我想出的（我不喜欢的东西，尤其是chat_clients桌子）： 在此处输入图像描述

解释：

表chats（聊天数据）：

  1. client_idchat_clients- 表示表 的id
  2. duration - 聊天时长（120 秒）
  3. system_type- 存储聊天的名称（Zopim，Chat2Desc，...）
  4. created_at - 创建日期

表 chat_clients（有关聊天中的用户的信息）：

  1. is_agent - 0 | 1:1 => 我的用户，0 => 不是我的
  2. user_id - 是用户 ID。包含来自 users 表的 id 或为空。
  3. assigned_data- 用户在聊天中的首字母缩写
  4. bean_module - 没关系（关于我的用户的信息）
  5. unique_col - 将有一封电子邮件（来自 Zopim）或电话（来自 Chat2Desc，或者我认为存储用户表的 id）。将保证值的唯一性。

users_id + unique_col 束是唯一的 ( UNIQUE KEY user_id_unique_col_UQ (user_id, unique_col))

表 chat_messages：

  1. text - 消息的文本。
  2. client_id - 表示chat_clients 表的id
  3. chat_id - 表示聊天桌的id
  4. file_id - 表示chat_files 表的id
  5. transport - 值将用于 Chat2Desc（Viber、WhatsApp、 ...），用于 Zopim，所以它不是空的，Zopim

表chat_files有关聊天中传输文件的信息。类比表可能不存储附加信息。

将来我将为每个用户选择通信历史。

问：如何改进表格结构以获得更大的灵活性？

先感谢您。

mysql database-design

3 个回答

  1. Best Answer

    所有现代聊天界面无一例外地实现了分层和非时间顺序的聊天模式。这意味着您必须使用（即将发布的）MySQL 8，因为之前的版本不支持执行此操作所需的递归 CTE。任何解决方法都不能允许无限深度，这通常是必需的，或者至少很高兴拥有。

    您可以在这里查看 PostgreSQL 中的模式，而不是在这里绘制模式，我在其中回答了一个类似的问题。本质上，每条消息都有指向同一张桌子上id|parent_id的点。这将创建一个。我们将这种层次结构的实现称为“自引用”或“单表层次结构”。parent_idid

    此外，您可能希望实现一组标记的用户等，以便更好地进行索引。

    最终，如果您打算走这条路并且之前没有任何工作，那么您应该使用 PostgreSQL——它现在支持递归 CTE，并且支持 sql-array 以便于标记。

    我并不是说您的架构必须看起来像那些。您可能有额外的要求，但我不想从劣质的功能集和错误的工具开始。

    为清楚起见，此批评是针对您的chat_messages桌子的。聊天会话将通过为其创建一个单独的表并将所有消息链接到该表中的条目来完成。例如，

    CREATE TABLE chat_session (
  chat_session_id  int  PRIMARY KEY
  ....
);

    其他表..

    CREATE TABLE chat_message (
  ts                             timestamp with time zone,
  chat_message_id                int PRIMARY KEY,
  chat_message_parent_id         int REFERENCES chat_message,
  chat_message_author_user_id    int REFERENCES user,
  chat_message_author_client_id  int REFERENCES client
);

    或之类的。

    • 12

  2. 首先，表chats和表之间的关系chat_clients是错误的方式。您需要很多chat_clientsper chats，因此您应该client_id从chats表中删除并添加chat_id到chat_clients表中。这将是一个外键chats(id)。

    chat_messages同样，和之间的关系chat_files是错误的方式 - 您希望（可能）每条消息有很多文件。因此，删除 chat_messages.file_id 并添加 chat_files.chat_message_id，并将其设为显式外键。

    您应该在表中添加一个 id 列（主键）users，并使其chat_clients.user_id成为外键。

    您将来可能会发现需要存储更多信息，例如为用户存储。然后，随着需求变得更加清晰，您可以添加额外的列（这通常不是问题，尤其是使用正确的工具，例如pt-online-schema-change），或者您现在可以通过添加更多通用列来预测这些需求然后，您的应用程序必须“解码”或解释。如果您使用的是 MySQL 5.7+，那么您甚至可以使用JSON 数据类型并将 JSON 数据存储在这些列中。

    • 0

  3. 客户端数据库

    通过将数据保存在 WhatsApp 和 Viber 等设备中，可以非常有效地减少存储在数据库中的数据。对于移动应用程序，这是设计数据库的最佳方式。

    服务器端聊天存储

    服务器端数据库

    市场上用于协作的网络聊天提供商，如 Slack、Hipchat，都是建立在服务器端数据库上的。对于基于在线社区的设置，它非常适合。

    客户端聊天存储

    有关更多信息，请参见此处。

    • 0

相关问题