MySQL - 复杂的层次关系 - 多个M:N？

Question

我有一个 MySQL 查询，它与 PHP 一起使用来为 d3.js 树/树状图构建分层 JSON。

查看架构和现有查询的小提琴。

我现在正在考虑如何在program_outcome数据O和单位数据U与development_level数据之间添加额外的数据关系D，之间为多对多关系。

只有三种类型的D，如下图所示。

我需要的概念图：

每个 U 仅与每个 O 的一个 D 相关（为清楚起见，仅显示一个 O 分支）。

所以 U 需要是 D 的子代和 O 的孙子。对于其他O分支，相同的U可能具有相同或不同类型的D。

正如您在小提琴中看到的，O 和 U 之间的关系当前是通过查找/关系表 program_outcome_unit_lookup 实现的。

此外，还可以更改查找表，因此可以有两个查找表，而不是 program_outcome_unit_lookup 表，这可能会起作用：

O -> U

D -> U

有什么想法可以实现这一点吗？

查询后的PHP（不在数据库小提琴中......）如下，但可能与解决方案无关，这本质上是数据库问题。

$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
    $data[$row->global_id] = $row;
}

$roots = array();
foreach ($data as $row) {   
    $row->type = end(explode(",",(implode(array_slice(explode ( ':',  $row->global_id), -2, 1)))));
    if ($row->parent_global_id === null) {
        $roots[]= $row;
    } else {
        $data[$row->parent_global_id]->children[] = $row;
    }
    unset($row->parent_global_id);
    unset($row->global_id);
}

$json = json_encode($roots);

$json = trim($json, '[]');

$fp = fopen('data.json', 'w');
fwrite($fp, $json);
fclose($fp);

更新

查看带有两个分支的扩展概念图：

Answer 1

您的模型似乎是：每个（唯一）元组 (O, U) 都被分配了一个强制值 D。

您可以通过向 program_outcome_unit_lookup 表添加 D 列来实现此模型：

CREATE TABLE `program_outcome_unit_lookup` (
  `program_outcome_unit_lookup_pk` int(6) NOT NULL AUTO_INCREMENT,
  `program_outcome_fk` int(2) NOT NULL,
  `devdata_fk` int(2) NOT NULL,
  `unit_fk` int(2) NOT NULL,
  PRIMARY KEY (`program_outcome_unit_lookup_pk`),
  UNIQUE KEY (`program_outcome_fk`, `unit_fk`)
);

(program_outcome_fk, unit_fk) 也可以是您的主键，但无论哪种方式，它都必须是唯一的（您当前没有强制执行此约束）。

现在，每个 U 都可以是任意多个 O 的成员，但根据要求，“每个 U 将仅与每个 D 关联”。

例如从更新的图表中存储 U1 （O1-D2-U1 和 O2-D1-U1），您可以将值 ((1,2,1),(2,1, 1))。根据要求，您还不能添加例如O2-D2-U1，因为它会违反唯一约束。

您还应该为 D 添加一个新表。如果不是每个 O 都允许使用每个 D （例如，如果不允许 O2 分支使用 D1），则还需要一个表 (O, D)，否则没有必要。