MySQL – komplexe hierarchische Beziehungen – mehrere M:N?

Question

Ich habe eine MySQL-Abfrage, die ich mit PHP verwende, um hierarchisches JSON für einen d3.js-Baum/Dendrogramm zu erstellen.

Fiddle zum Anzeigen des Schemas und vorhandener Abfragen.

Ich denke jetzt darüber nach, wie ich eine zusätzliche Datenbeziehung D zwischen ProgrammergebnisdatenO und EinheitendatenU und Entwicklungsebenendaten hinzufügen kann, bei der es sich um eine Viele-zu-Viele-Beziehung handelt.

Es gibt nur drei Arten von D, wie im Bild unten gezeigt.

Konzeptzeichnung, die ich brauche:

Jedes U ist nur mit einem D von jedem O verbunden (aus Gründen der Übersichtlichkeit wird nur ein O-Zweig angezeigt).

Du musst also ein Nachkomme von D und ein Enkel von O sein. Für andere O-Zweige kann dasselbe U den gleichen oder einen anderen Typ von D haben.

Wie Sie in der Geige sehen können, wird die Beziehung zwischen O und U derzeit über eine Nachschlage-/Beziehungstabelle program_outcome_unit_lookup implementiert.

Außerdem ist es möglich, die Nachschlagetabelle zu ändern, sodass Sie anstelle der program_outcome_unit_lookup-Tabelle zwei Nachschlagetabellen haben können, die möglicherweise funktionieren:

O -> U

D -> U

Irgendwelche Ideen, wie man das erreichen kann?

Das PHP nach der Abfrage (nicht in der Datenbank-Fiddle...) lautet wie folgt, ist jedoch möglicherweise nicht für die Lösung relevant, da es sich im Wesentlichen um ein Datenbankproblem handelt.

$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);

Aktualisiert

Sehen Sie sich das erweiterte Konzeptdiagramm mit zwei Zweigen an:

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)，否则没有必要。