MySQL - complex hierarchical relationships - multiple M:N?

Question

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:

Answer 1

Your model appears to be: each (unique) tuple (O, U) is assigned a mandatory value D.

You can implement this model by adding the D column to the program_outcome_unit_lookup table:

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) can also be your primary key, but either way it must be unique (you are not currently enforcing this constraint).

Now, each U can be a member of any number of O, but as per the requirement, "each U will be associated only with each D Association”.

For example to store U1 (O1-D2-U1 and O2-D1-U1) from the updated chart, you can change the values ​​ ((1,2,1),(2,1, 1)). As required, you cannot yet add e.g. O2-D2-U1 as it would violate the unique constraint.

You should also add a new table for D. If not every O is allowed to use every D (e.g. if the O2 branch is not allowed to use D1), then one more Table (O, D), otherwise it is not necessary.