mysql - 如何使用 MySQL 存储这种分层数据？

Question

我目前正在设计一个将被许多企业使用的网络应用程序。但是，我无法决定如何存储数据。数据的一般结构在这棵树中展示:http://i.imgur.com/lpYwqya.png

所以会有一个表格列出每个客户。每个客户都有自己的用户和项目。每个项目都有两个 child :用户和任务。用户是指在客户端下注册的允许访问该项目的用户(将存储该用户的id，以及他们的权限[读/写])对于树的每一层，我需要存储数据。例如，任务具有以下字段(WBS、名称、开始日期、完成日期、持续时间、工时、成本、固定成本、供应商...)

我很难决定如何最好地构建数据。请注意，数据将始终从树的顶部向下访问( parent 到 child )，我永远不必跨 child 移动或备份树。这是我想出的两个解决方案:

解决方案 1:拥有无限数量的表。每次创建客户端时，也会创建两个表:1_projects 和 1_users(其中 1 是第一个表中客户端的 ID)。创建项目时，会创建表1_1_tasks，依此类推。因此，ID 为 5、任务 ID 为 3895、项目 ID 为 19 和客户 ID 为 57658 的风险的计划表为:57658_19_3895_5_plans。

解决方案 2:有 9 个表:clients、users、projects、project_users、tasks、risks、risk_updates、plans、plan_updates。在风险表中，除了每个风险与之相关的字段外，它还会有以下内容:client_id、project_id、task_id。因此，例如，如果我想返回客户对特定任务的所有风险，我会在整个树中搜索 client_id = #、project_id = #、task_id = # 的风险。当然，这些字段将形成风险表的复合键。因此，风险表将存储每个任务、每个项目、每个客户的风险。最后一个表 plan_updates 显然会很大。

我认为解决方案 1 很强大，因为它允许我轻松地向下导航树，因为不属于同一父级的节点不会存储在同一个表中。但是，这种方案也很糟糕，因为会有大量的表，所以以后对数据库的任何修改都非常困难。

解决方案 2 很强大，因为所有风险都集中在一张表中。但是，我想知道它在搜索 plan_updates 表时是否会非常低效，因为我将不得不在整个表(这将是巨大的)中搜索与所有父元素的 ID 匹配的字段。

为了正确看待这一切，我预计会出现以下情况:

用户:每个客户 1-20 个。通常少于 5 个。

项目:每个客户 1-100 个。大多数将少于 20。

任务:每个项目 100-10,000。

风险:每项任务 0-10。不过，只有大约 30% 的任务会有风险，其中大部分只会有 1-4 种风险。

风险更新:每个风险 1-10。

计划:每个风险 1-5 个。

计划更新:每个计划 1-10。

如果有人能阐明我如何最好地解决这个问题，那将非常有帮助。

Answer 1

第二个解决方案对我来说似乎更合理。第一个解决方案的最大缺陷是整个结构的可管理性差。您很快就会得到大量的表，如果结构发生变化(需要添加额外的字段或额外的约束)，您将遇到麻烦。

另一方面，您对复合键的担忧并不那么严重。

例如，任务可以单独分配给单个项目。他们也不需要直接引用客户。另一方面，您很可能会在某个时候引入另一个直接连接用户和任务的 n-n 链接表，以便定义谁将执行该特定任务。

因此，如果您想列出一项任务的所有风险，您首先必须找到手头的任务，然后使用一个键(任务 ID)扫描风险表。无论您有一张 table 还是多张 table ，这都是一样的。

我强烈建议您选择解决方案 #2，并确保您识别所有相关的主键和索引(以及适用的唯一列)。这将使数据库快速高效。

编辑

正如@MSW 提到的，关于这个主题还有很多要说的。关于涵盖该主题的数据库设计(具有正态性、原子性等原则......)的文献不胜枚举。

还有一点可以解释解决方案 #1 的质量不佳，因为稍后您将无法轻松地跨各种项目进行分析，因为它们都在一个大型项目中不同表的数量。