MySQL - complex hierarchical relationships - multiple M:N?-PHP Chinese Network Q&A

I have a MySQL query that I use with PHP to build hierarchical JSON for a d3.js tree/dendrogram.

Fiddle to view schema and existing queries.

I am now considering how to add additional data relationships D between program_outcome data O and unit data U and development_level data , with multiple pairs between them Many relationships.

There are only three types of D, as shown in the figure below.

Concept map I need:

Each U is related to only one D of each O (only one O branch is shown for clarity).

So U needs to be the descendant of D and the grandson of O. The same U may have the same or different type of D for other O branches.

As you can see in the fiddle, the relationship between O and U is currently implemented via the lookup/relationship table program_outcome_unit_lookup.

Also, it is possible to change the lookup tables so there are two lookup tables instead of the program_outcome_unit_lookup table, which might work:

O -> U

D -> U

Any ideas how to achieve this?

The PHP after the query (not in the database fiddle...) is as follows, but may not be relevant to the solution, this is essentially a database issue.

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

renew

View the expanded concept diagram with two branches:

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