mysql - 多个外键列与多个连接表

Question

这是另一个数据库规范化问题/讨论，但我希望对多个外键列与多个连接/交叉表的不同场景的权衡、优点和缺点有一些额外的看法，因为我似乎找不到有关 MYSQL 如何在不同方法上优化或失败的任何实用信息或建议。

我问的是其他人如何处理具有多个 1:N 关系和外键的对象的一般指导，但它们中的大多数将始终为空。

作为一个基本示例，假设我有一个项目管理应用程序，其中包含一个uploads用于存储上传文件信息的表。对于“规模”，当前有 2000 万次上传，每天增加 1000 次。

上传可以与几个不同的对象直接相关，因为它是“父对象”或“所有者”。直接到项目、直接到待办事项和直接到评论。每个上传一次只能有一个关系，永远不会有多个。

我看到的结构化潜在选择

方案一：单表多外键列

上传 upload_id, filepath, project_id, todo_id, comment_id

project_id、todo_id、comment_id 的外键。

潜在问题：外键中有大量空值。由于 fk 约束和额外的索引大小，大量写入/锁定可能会变慢。

选项 2：多个交叉/连接表

上传 upload_id, filepath

项目上传 project_id, upload_id

todo_uploads todo_id, upload_id

评论上传 comment_id, upload_id

*_uploads 表的所有列的外键

潜在问题：人们会混淆 N:N 而不是 1:N 关系。“相对”，但在应用程序层中产生更难的选择，尤其是在为项目选择上传时，因为您需要加入所有表以获取上传的项目 ID 的整个列表，因为待办事项和评论也都属于父项.

选项 3：具有类型的单一关系/连接表

上传 upload_id, filepath

objects_uploads upload_id, object_id, type upload_id 上的外键，object_id 上的标准索引和类型。

潜在问题：更混乱的模式，不是真正的“关系”或规范化

我还想放弃在单个对象上使用 JSON 字段的可能性，并且总是在上传时强制执行 project_id。我对 JSON 字段类型或其陷阱的经验非常有限。我假设选择将上传特别作为父/上传到待办事项或评论会更加困难，因为您需要从 json 中选择 id。

还有其他我忽略的方法或考虑因素吗？他们的具体考虑是否基于不同的工作负载、更高的写入量、高读取等。感谢您提供任何信息、见解或资源。

编辑 为了澄清，我知道上面的轮廓可以代表对象的方案/关系的差异。我真的只考虑编写和选择性能以及围绕索引/约束和连接进行的考虑或权衡。特别是对于这个问题，我不太关心引用完整性或 100% 数据完整性。

我在上面的原始示例中修改了一些语言。我正在寻找可以与许多不同对象相关的对象的理想配置，但不会同时使大多数外键列为空。这是 3.5 年前的类似问题...... https://softwareengineering.stackexchange.com/questions/335284/disadvantages-of-using-a-nullable-foreign-key-instead-of-creating-an-intersectio

基本上试图找到一些一般建议何时以不同方式考虑或优化，衡量外键中大量空值的任何实际影响以及何时更喜欢不同方法的潜在原因。

标签： mysqldatabasejoinforeign-keysdatabase-normalization

Answer 1

选项 1 模拟三个多对一关系。也就是说，一个给定的上传最多可以有一个对项目的引用，最多一个对 todo 的引用，以及最多一个对评论的引用。这将是一种将这些建模为多对一关系的简单方法。不要回避使用 NULL，它们不会占用存储空间。

选项 2 模拟三个多对多关系。给定的上传可能与多个项目、多个待办事项和多个评论相关联。我想这就是 Akina 在上面评论的内容。如果您的应用程序需要这些是多对多关系，那么您需要这三个交集表来为该数据建模。如果您不需要这些是多对多关系，则不要创建这些表。

选项 3 根本不是关系数据模型。它与几种正常形式相冲突。