CODEWITHSUNDEEP
phpmysql
IlludiumPu36
IlludiumPu36

Reputation: 4304

MySQL - Complex hierarchical relationships - multiple M:N?

I have a MySQL query which is used with PHP to build hierarchical JSON for a d3.js tree/dendrogram.

See fiddle for schema and existing query.

I'm now looking at how I can add an additional data relationship between program_outcome data, O, and unit data, U, with development_level data, D, in between as a many to many relationship.

There are only three types of D as you can see in the diagram below.

Conceptual diagram of what I need:

enter image description here

Each U will be related to only one D per O (only one O branch shown for clarity).

So U needs to be a child of D and a grandchild of O. For other O branches, the same U may have the same or different type of D.

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

Also, there is the possibility of changing the lookup table, so instead of having program_outcome_unit_lookup table, there could be two lookup tables, which might work:

O -> U

D -> U

Any ideas how this would be achieved?

The PHP after the query (not in the db fiddle...) is following, but may not be relevant to a solution, which is essentially a DB problem.

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

UPDATE

See the expanded conceptual diagram with two branches:

enter image description here

Upvotes: 0

Views: 199

Answers (1)

Solarflare
Solarflare

Reputation: 11116

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

You can implement this model by adding a column D to your 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) could be your primary key as well, but either way it has to be unique (you are currently not enforcing this constraint).

Now each U can be member of as many O as you like, but "each U will be related to only one D per O", as requested.

To e.g. store U1 from your updated graph (O1-D2-U1 and O2-D1-U1), you would add into (O,D,U) the values ((1,2,1),(2,1,1)). As requested, you cannot also 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 D is allowed for every O (e.g. if O2 branches are not allowed to use D1), you will also need a table (O, D), otherwise it is not necessary.

Upvotes: 1

Related Questions