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)，否則沒有必要。