MySQL – relations hiérarchiques complexes – plusieurs M:N ?

Question

J'ai une requête MySQL que j'utilise avec PHP pour créer un JSON hiérarchique pour un arbre/dendrogramme d3.js.

Fiddle pour afficher le schéma et les requêtes existantes.

Je réfléchis maintenant à la façon d'ajouter une relation de données supplémentaire D entre les données de résultat du programmeO et les données unitairesU et les données de niveau de développement, qui est une relation plusieurs-à-plusieurs.

Il n'existe que trois types de D comme le montre l'image ci-dessous.

Dessin conceptuel dont j'ai besoin :

Chaque U est lié à un seul D de chaque O (une seule branche O est affichée pour plus de clarté).

Vous devez donc être un descendant de D et un petit-fils de O. Pour les autres branches O, le même U peut avoir le même type de D ou un type différent.

Comme vous pouvez le voir sur le violon, la relation entre O et U est actuellement implémentée via une table de recherche/relation program_outcome_unit_lookup.

De plus, il est possible de changer la table de recherche, donc au lieu de la table program_outcome_unit_lookup, vous pouvez avoir deux tables de recherche, qui pourraient fonctionner :

O -> U

D -> U

Des idées pour y parvenir ?

Le PHP après la requête (pas dans le violon de la base de données...) est le suivant, mais peut ne pas être pertinent pour la solution, il s'agit essentiellement d'un problème de base de données.

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

Mise à jour

Voir le diagramme de concept étendu avec deux branches :

Answer 1

Votre modèle semble être : chaque tuple (unique) (O, U) 都被分配了一个强制值 D.

Vous pouvez implémenter ce modèle en ajoutant des colonnes à 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) peut également être votre clé primaire, mais dans tous les cas, elle doit être unique (vous n'appliquez pas actuellement cette contrainte).

Maintenant, chaque U 都可以是任意多个 O 的成员，但根据要求，“每个 U 将仅与每个 Dassociation”.

Par exemple, stocker U1 （O1-D2-U1 和 O2-D1-U1），您可以将值 ((1,2,1),(2,1, 1))。根据要求，您还不能添加例如O2-D2-U1 à partir du graphique mis à jour, car cela violerait la contrainte unique.

Vous devez également prévoir D 添加一个新表。如果不是每个 O 都允许使用每个 D （例如，如果不允许 O2 分支使用 D1），则还需要一个表 (O, D) pour

, sinon ce n'est pas nécessaire. 🎜