Quelles sont les options pour stocker des données hiérarchiques dans une base de données relationnelle?

ma réponse préférée est comme ce que la première phrase dans ce fil suggéré. Utilisez une liste de contiguïté pour maintenir la hiérarchie et utilisez des ensembles imbriqués pour interroger la hiérarchie.

le problème jusqu'à présent a été que la méthode de couverture d'une liste adjacente aux ensembles imbriqués a été affreusement lent parce que la plupart des gens utilisent la méthode extrême RBAR connu comme une "pile Push" pour faire la conversion et a été considéré comme moyen de cher pour atteindre le Nirvana de la simplicité de la maintenance par la liste de contiguïté et la performance impressionnante des ensembles imbriqués. En conséquence, la plupart des gens finissent par devoir se contenter de l'un ou l'autre, surtout s'il y a plus de, disons, 100 000 noeuds minables. L'utilisation de la méthode push stack peut prendre une journée entière pour faire la conversion sur ce que MLM'ers considérerait être une petite hiérarchie de million de noeuds.

J'ai pensé que je donnerais à Celko un peu de concurrence en proposant une méthode pour convertir un Liste de contiguïté aux ensembles imbriqués à des vitesses qui semblent tout simplement impossible. Voici la performance de la méthode push stack sur mon portable i5.

Duration for     1,000 Nodes = 00:00:00:870 
Duration for    10,000 Nodes = 00:01:01:783 (70 times slower instead of just 10)
Duration for   100,000 Nodes = 00:49:59:730 (3,446 times slower instead of just 100) 
Duration for 1,000,000 Nodes = 'Didn't even try this'

et voici la durée de la nouvelle méthode (avec la méthode push stack entre parenthèses).

Duration for     1,000 Nodes = 00:00:00:053 (compared to 00:00:00:870)
Duration for    10,000 Nodes = 00:00:00:323 (compared to 00:01:01:783)
Duration for   100,000 Nodes = 00:00:03:867 (compared to 00:49:59:730)
Duration for 1,000,000 Nodes = 00:00:54:283 (compared to something like 2 days!!!)

Oui, c'est exact. 1 million de noeuds convertis en moins d'une minute et 100 000 noeuds en moins de 4 secondes.

vous pouvez lire à propos de la nouvelle méthode et obtenir une copie de la code à l'adresse suivante. http://www.sqlservercentral.com/articles/Hierarchy/94040 /

j'ai également développé une hiérarchie" pré-agrégée " en utilisant des méthodes similaires. Cet article intéressera tout particulièrement les membres du MLM et les personnes qui rédigent des factures de matériaux. http://www.sqlservercentral.com/articles/T-SQL/94570 /

si vous vous arrêtez pour jeter un coup d'oeil à l'un ou l'autre des articles, sautez dans le discussion" et laissez-moi savoir ce que vous en pensez.

ce dessin n'a pas encore été mentionné:

Plusieurs colonnes de lignage

malgré ses limites, si vous pouvez les supporter, c'est très simple et très efficace. Features:

• colonnes: une pour chaque niveau de lignée, se réfère à tous les parents jusqu'à la racine, les niveaux au-dessous du niveau des articles courants sont définis à NULL
• limite à la profondeur de la hiérarchie peut être
• ancêtres bon marché, descendants, niveau
• insertion Bon Marché, supprimer, déplacement des feuilles
• insertion coûteuse, suppression, déplacement des noeuds internes

voici un exemple-arbre taxonomique d'oiseaux de sorte que la hiérarchie est Classe/ordre/Famille/genre/espèce - l'espèce est le niveau le plus bas, 1 Rangée = 1 taxon (qui correspond à l'espèce dans le cas des noeuds foliaires):

CREATE TABLE `taxons` (
  `TaxonId` smallint(6) NOT NULL default '0',
  `ClassId` smallint(6) default NULL,
  `OrderId` smallint(6) default NULL,
  `FamilyId` smallint(6) default NULL,
  `GenusId` smallint(6) default NULL,
  `Name` varchar(150) NOT NULL default ''
);

et l'exemple des données:

+---------+---------+---------+----------+---------+-------------------------------+
| TaxonId | ClassId | OrderId | FamilyId | GenusId | Name                          |
+---------+---------+---------+----------+---------+-------------------------------+
|     254 |       0 |       0 |        0 |       0 | Aves                          |
|     255 |     254 |       0 |        0 |       0 | Gaviiformes                   |
|     256 |     254 |     255 |        0 |       0 | Gaviidae                      |
|     257 |     254 |     255 |      256 |       0 | Gavia                         |
|     258 |     254 |     255 |      256 |     257 | Gavia stellata                |
|     259 |     254 |     255 |      256 |     257 | Gavia arctica                 |
|     260 |     254 |     255 |      256 |     257 | Gavia immer                   |
|     261 |     254 |     255 |      256 |     257 | Gavia adamsii                 |
|     262 |     254 |       0 |        0 |       0 | Podicipediformes              |
|     263 |     254 |     262 |        0 |       0 | Podicipedidae                 |
|     264 |     254 |     262 |      263 |       0 | Tachybaptus                   |

C'est super, car de cette façon vous accomplir toutes les opérations d'une manière très facile, aussi longtemps que les catégories ne change pas de niveau dans l'arborescence.

C'est une réponse très partielle à votre question, mais j'espère encore utile.

Microsoft SQL Server 2008 implémente deux fonctionnalités extrêmement utiles pour gérer les données hiérarchiques:

• le HierarchyId type de données.
• expressions courantes de table, en utilisant le avec mot-clé.

regarder "Modèle de Votre Hiérarchie des données Avec SQL Server 2008" par Kent Tegels sur MSDN pour les mises en chantier. Voir aussi ma propre question: requête récursive table identique dans SQL Server 2008

je suis allé pour elle parce que je pouvais insérer de nouveaux éléments à l'arbre facilement (vous avez juste besoin de l'id d'une branche pour insérer un nouvel élément à elle) et aussi la requête assez rapide.

+-------------+----------------------+--------+-----+-----+
| category_id | name                 | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
|           1 | ELECTRONICS          |   NULL |   1 |  20 |
|           2 | TELEVISIONS          |      1 |   2 |   9 |
|           3 | TUBE                 |      2 |   3 |   4 |
|           4 | LCD                  |      2 |   5 |   6 |
|           5 | PLASMA               |      2 |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |      1 |  10 |  19 |
|           7 | MP3 PLAYERS          |      6 |  11 |  14 |
|           8 | FLASH                |      7 |  12 |  13 |
|           9 | CD PLAYERS           |      6 |  15 |  16 |
|          10 | 2 WAY RADIOS         |      6 |  17 |  18 |
+-------------+----------------------+--------+-----+-----+

• chaque fois que vous avez besoin de tous les enfants d'un parent, il vous suffit d'interroger la colonne parent .
• si vous avez besoin de tous les descendants de n'importe quel parent vous interrogez pour les éléments qui ont leur lft entre lft et rgt de parent.
• si vous avez besoin de tous les parents de n'importe quel noeud jusqu'à la racine de l'arbre, vous demandez pour les articles ayant lft plus bas que le noeud lft et rgt plus grand que le noeud rgt et trier le par parent .

"j'avais besoin de rendre l'accès et le questionnement de l'arbre plus rapide que les inserts, c'est pourquoi j'ai choisi ce

le seul problème est de corriger les colonnes left et right lors de l'insertion de nouveaux éléments. Eh bien, j'ai créé une procédure stockée pour elle et l'a appelé chaque fois que j'ai inséré un nouvel élément qui était rare dans mon cas, mais il est vraiment rapide. J'ai eu l'idée du Livre de Joe Celko, et la procédure stockée et comment je suis arrivé à elle est expliquée ici en DBA SE https://dba.stackexchange.com/q/89051/41481

si votre base de données supporte des tableaux, vous pouvez aussi implémenter une colonne de lignée ou un chemin matérialisé comme un tableau d'ids parent.

spécifiquement avec Postgres vous pouvez alors utiliser les opérateurs de jeu pour interroger la hiérarchie, et obtenir d'excellentes performances avec des indices de GIN. Cela rend la recherche des parents, des enfants, et de la profondeur assez triviale dans une seule requête. Les mises à jour sont assez gérable.

j'ai une écriture complète de l'utilisation des tableaux pour les chemins matérialisés si vous êtes curieux.

c'est vraiment une question de point carré, de trou rond.

si les bases de données relationnelles et SQL sont le seul marteau que vous avez ou que vous êtes prêt à utiliser, alors les réponses qui ont été postées jusqu'à présent sont adéquates. Cependant, pourquoi ne pas utiliser un outil conçu pour gérer des données hiérarchiques? base de données Graphique sont idéales pour les données hiérarchiques complexes.

les inefficacités du modèle relationnel ainsi que les complexités de toute solution de code / requête pour mapper un graphe / modèle hiérarchique sur un modèle relationnel ne vaut tout simplement pas l'effort par rapport à la facilité avec laquelle une solution de base de données de graphe peut résoudre le même problème.

considère une nomenclature de matériaux comme une structure hiérarchique commune de données.

class Component extends Vertex {
    long assetId;
    long partNumber;
    long material;
    long amount;
};

class PartOf extends Edge {
};

class AdjacentTo extends Edge {
};

chemin le plus court entre deux sous-ensembles : algorithme de traversée de graphe Simple. Les parcours acceptables peuvent être qualifiés sur la base de critères.

similitude : Quel est le degré de similitude entre deux assemblages? Effectuer une traversée sur les deux sous-arbres en calculant l'intersection et l'union des deux sous-arbres. Le pourcentage similaire est l'intersection divisée par le syndicat.

Transitive Closure : marchez dans le sous-arbre et résumez le(s) champ (s) d'intérêt, par exemple "combien d'aluminium se trouve dans un sous-ensemble?"

Oui, vous peut résoudre le problème avec SQL et une base de données relationnelle. Cependant, il ya beaucoup d'approches mieux si vous êtes prêt à utiliser le bon outil pour le travail.

J'utilise PostgreSQL avec des tables de fermeture pour mes hiérarchies. J'ai une procédure stockée universelle pour toute la base de données:

CREATE FUNCTION nomen_tree() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
  old_parent INTEGER;
  new_parent INTEGER;
  id_nom INTEGER;
  txt_name TEXT;
BEGIN
-- TG_ARGV[0] = name of table with entities with PARENT-CHILD relationships (TBL_ORIG)
-- TG_ARGV[1] = name of helper table with ANCESTOR, CHILD, DEPTH information (TBL_TREE)
-- TG_ARGV[2] = name of the field in TBL_ORIG which is used for the PARENT-CHILD relationship (FLD_PARENT)
    IF TG_OP = 'INSERT' THEN
    EXECUTE 'INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT .id,.id,0 UNION ALL
      SELECT .id,ancestor_id,depth+1 FROM ' || TG_ARGV[1] || ' WHERE child_id=.' || TG_ARGV[2] USING NEW;
    ELSE                                                           
    -- EXECUTE does not support conditional statements inside
    EXECUTE 'SELECT .' || TG_ARGV[2] || ',.' || TG_ARGV[2] INTO old_parent,new_parent USING OLD,NEW;
    IF COALESCE(old_parent,0) <> COALESCE(new_parent,0) THEN
      EXECUTE '
      -- prevent cycles in the tree
      UPDATE ' || TG_ARGV[0] || ' SET ' || TG_ARGV[2] || ' = .' || TG_ARGV[2]
        || ' WHERE id=.' || TG_ARGV[2] || ' AND EXISTS(SELECT 1 FROM '
        || TG_ARGV[1] || ' WHERE child_id=.' || TG_ARGV[2] || ' AND ancestor_id=.id);
      -- first remove edges between all old parents of node and its descendants
      DELETE FROM ' || TG_ARGV[1] || ' WHERE child_id IN
        (SELECT child_id FROM ' || TG_ARGV[1] || ' WHERE ancestor_id = .id)
        AND ancestor_id IN
        (SELECT ancestor_id FROM ' || TG_ARGV[1] || ' WHERE child_id = .id AND ancestor_id <> .id);
      -- then add edges for all new parents ...
      INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT child_id,ancestor_id,d_c+d_a FROM
        (SELECT child_id,depth AS d_c FROM ' || TG_ARGV[1] || ' WHERE ancestor_id=.id) AS child
        CROSS JOIN
        (SELECT ancestor_id,depth+1 AS d_a FROM ' || TG_ARGV[1] || ' WHERE child_id=.' 
        || TG_ARGV[2] || ') AS parent;' USING OLD, NEW;
    END IF;
  END IF;
  RETURN NULL;
END;
$_$;

puis pour chaque table où j'ai une hiérarchie, je crée un déclencheur

CREATE TRIGGER nomenclature_tree_tr AFTER INSERT OR UPDATE ON nomenclature FOR EACH ROW EXECUTE PROCEDURE nomen_tree('my_db.nomenclature', 'my_db.nom_helper', 'parent_id');

pour remplir une table de fermeture à partir d'une hiérarchie existante, j'utilise cette procédure stockée:

CREATE FUNCTION rebuild_tree(tbl_base text, tbl_closure text, fld_parent text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    EXECUTE 'TRUNCATE ' || tbl_closure || ';
    INSERT INTO ' || tbl_closure || ' (child_id,ancestor_id,depth) 
        WITH RECURSIVE tree AS
      (
        SELECT id AS child_id,id AS ancestor_id,0 AS depth FROM ' || tbl_base || '
        UNION ALL 
        SELECT t.id,ancestor_id,depth+1 FROM ' || tbl_base || ' AS t
        JOIN tree ON child_id = ' || fld_parent || '
      )
      SELECT * FROM tree;';
END;
$$;

sont définies avec 3 colonnes - ANCÊTRE_ID, DESCENDANT_ID, profondeur. Il est possibilité (et même conseil) de stocker des enregistrements avec la même valeur pour L'ancêtre et le DESCENDANT, et une valeur de zéro pour la profondeur. Cela simplifiera les requêtes pour la récupération de la hiérarchie. Et ils sont très simples en effet:

-- get all descendants
SELECT tbl_orig.*,depth FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth <> 0;
-- get only direct descendants
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth = 1;
-- get all ancestors
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON ancestor_id = tbl_orig.id WHERE descendant_id = XXX AND depth <> 0;
-- find the deepest level of children
SELECT MAX(depth) FROM tbl_closure WHERE ancestor_id = XXX;