mysql - 数据库设计 : Referencing multiple 1 to 0. .1 关系

Question

我有许多不同的消息类型(比方说 20 种)，我需要按照以下方式根据它们的日期和类型进行选择

即... WHERE date BETWEEN [fromDate] AND [toDate] AND type = [0 - 20 different types]

不同类型的消息有很少的共同列(日期是最重要的)，但我总是需要“一次性”获取所有类型的评论，按日期排序。消息有一个 self 引用以允许线程对话。一条消息总是一种类型，而且只有一种类型。

因为我在存档中有 5.000.000 条消息，而一次对话中的消息很少超过 50 条，所以我需要能够按日期或对话标识符高效地进行选择。因此，我有一个“所有消息之母”表和多个额外的表，它们与消息表有 1 .. 0-1 关系:

messages:   [id, date, parent_id (nullable), ... ]
msgs_type1: [col1, col2, col3, col4, ...]
msgs_type2: [col1, col2, col3, col4, ...]

然后这是我的问题:您通常如何指定这些类型的表之间的关系？例如，加入表格的(缺点)优点是什么？以下方式:

messages: [id, date, parent_id (null), **msg_type_1 (null), msg_type_2 (null)**, ...]
msgs_type1: [col1, col2, col3, col4]
msgs_type2: [col1, col2, col3, col4]
...

(消息中指定的可选关系)

messages: [id, date, **type**, parent_id (null)]
msgs_type1: [**message_id**, col1, col2, col3, col4]
msgs_type2: [**message_id**, col1, col2, col3, col4]
...

(msgs_type表中指定的强制关系，message中指定的查找表)

一方面，有 20 个可选列感觉很脏，其中(只有)一列必须有一个值才能指定消息的类型。

另一方面，有一个“类型”枚举列，并使用它来手动推断要在哪个表中查找附加信息也感觉不对——并且可能会在大多数 ORM 中造成很大的痛苦.

那么对于这些​​类型的结构，这本书是怎么说的呢？如果有一天我收到 200 条不同类型的消息呢？

Answer 1

恕我直言:任何时候您都必须更改您的数据库，因为您已经添加了“某物”的新“类型”，正如他们所说，做错了。我对这种类型的面向列的表感到满意的唯一情况是它是在刚刚生成报告之前完成的。或者，也许，在流程结束时完成，以简化可能想要生成自己的查询的非技术用户的工作。

具有 5 到 1000 万行的正确索引和规范化的表结构应该仍然可以正常运行。