Discussion :

[Greg221]

Bonjour,

J'ai développé une base de donnée basée sur une structure hiérarchique (l'application est développée sur un modele composite) et je cherche à résoudre plusieurs problèmes :

- Comment limiter au maximum la dépendance entre mes différentes tables : je veux pouvoir ajouter des noeuds intermédiaires facilement et également changer l'ordre de dépendance.

- Comment optimiser la performance d'une telle relation : création de vues ou simples jointures ?

La situation
------------

- toutes mes tables sont liées les unes autres par une relation parentID :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Table1 (idT1, desc)
Table2(idT2, desc, ..., Table1ID)
Table3(idT3, desc, ..., Table2ID)
...
Table8(idT8, desc, ..., Table7ID)

- on peut lier plusieurs tables à une seule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Table6(idT6,desc, ..., Table5ID)
Table7(idT7,desc, ..., Table5ID)

Objectif
----------

Limiter la dépendance :
J'aimerai, par exemple, ajouter facilement une tableX entre mes tables 2 et 3. Cela me demande donc d'effectuer quelques débranchements/rebranchements... ce que je cherche à éviter.
En clair, je cherche une solution pour externaliser l'ordonnancement de mes tables.
Une solution serait peut-être d'utiliser une table de gestion "OrdreTable" dans laquelle on aurait les champs :
id, parentID, table_name

Cette table contiendrait donc les différentes relations entre tables, exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
1, NULL, Table1
2, 1, Table2
3, 2, Table3
4, 2, Table4 (ici j'attache ma Table4 à la table2)
...

Et dans chacune des tables on aurait un champ "parentId" (champ qui pointe sur la table définit par "OrdreTable")
Cela dit, je ne sais pas si c'est la meilleure solution.... d'où mon message.


Merci d'avance

edit : un internaute évoque ce problème (sans solution) : http://forums.mysql.com/read.php?125...741#msg-154741
(différence : je ne gère pas les champs "child" mais le problème est le même)

il existe des tutoriaux sur ce fameux modèle "data-driven join table" ?

[fsmrel]

Ce genre de traitement de la hiérarchie vous convient-il (SQL Server 2005) ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Create table MaTable (  
    TableId     Integer      Not Null
  , TableNom    Varchar(48)  Not Null 
 , Constraint T1 Primary Key (TableId)
) ;
 
Create table Hierarchie (
    TableId     Integer      Not Null
  , ParentId    Integer      Not Null
 , Constraint H1 Primary Key (TableId)
 , Constraint H2 Foreign Key (TableId) References MaTable
        On delete Cascade
 , Constraint H3 Foreign Key (ParentId) References MaTable
        On delete No Action
 , Constraint H4 Check (ParentId <> TableId)
) ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Insert Into MaTable Values (1, 'TABLE 1') 
Insert Into MaTable Values (2, 'TABLE 2') 
Insert Into MaTable Values (3, 'TABLE 3') 
Insert Into MaTable Values (4, 'TABLE 4') 
Insert Into MaTable Values (5, 'TABLE 5') 
Insert Into MaTable Values (6, 'TABLE 6') 
Insert Into MaTable Values (7, 'TABLE 7') 
Insert Into MaTable Values (8, 'TABLE 8')

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Insert Into Hierarchie Values (2, 1)
Insert Into Hierarchie Values (3, 2)
Insert Into Hierarchie Values (4, 2)
Insert Into Hierarchie Values (6, 5)
Insert Into Hierarchie Values (7, 5)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Select   x.*, y.TableNom
      , (Select z.TableNom 
         From   MaTable z 
         Where  z.TableId = x.ParentId) As ParenteNom 
from Hierarchie x Inner join MaTable y ON x.TableId = y.TableId

=>

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
TableId    ParentId    TableNom
   2          1         table 2
   3          2         table 3
   4          2         table 4
   6          5         table 6
   7          5         table 7

[Greg221]

Merci,

je réfléchis en terme de perf (pour faire les jointures) sachant que j'ai une dizaine de tables contenant chacune des dizaines de milliers de lignes....

[Greg221]

Le problème avec ton modèle c'est qu'on perd la hiérarchie des données.

J'avais pensé à un modèle (sur 2 niveaux) comme (pas forcement la bonne syntaxe) :

//l'idée est de créer une table Hiérarchie qui contient l'arborescence des données des différentes tables.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE Table1 (  
    Id     Integer AUTO_INCREMENT     NOT NULL
  , Parent_ID Integer       NOT NULL,
 , DESC VARCHAR(10) NULL
 , Constraint T1 PRIMARY KEY (Id)
 , Constraint T2 FOREIGN KEY (Parent_ID) REFERENCES Hierarchie
) ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE Table2 (  
    Id     Integer   AUTO_INCREMENT    NOT NULL
  , Parent_ID Integer       NOT NULL 
 , DESC VARCHAR(10) NULL
 , Constraint T1 PRIMARY KEY (Id)
 , Constraint T2 FOREIGN KEY (Parent_ID) REFERENCES Hierarchie
) ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
 
CREATE TABLE Hierarchie (  
    Id     Integer AUTO_INCREMENT     NOT NULL
  , Parent_ID Integer       NOT NULL ,
  , TableINFO VARCHAR(60)
 , Constraint T1 PRIMARY KEY (Id)
 , Constraint T2 FOREIGN KEY (Parent_ID) REFERENCES Hierarchie 
) ;

//ajout d'une donnée dans ma table1
//pas de niveau supérieur donc NULL à parent_id

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO Hierarchie(id, parent_id, TAbleINFO) VALUES('', NULL, Table1)
ID_T1 = "SELECT LAST_INSERT_ID";
INSERT INTO Table1(id, parent_id, desc) VALUES('', ID_T1, "Desc...");

//ajout d'une donnée (dans Table2) liée à ma précédente donnéee ajoutée dans la Table1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO Hierarchie(id, parent_id, TAbleINFO) VALUES('', ID_T1, Table2)
ID_T2 = "SELECT LAST_INSERT_ID";
INSERT INTO Table1(id, parent_id, desc) VALUES('', ID_T2, "Desc2...");

et ainsi de suite....

Au final, je me retrouve avec une table hierarchie qui contient un nombre d'enregistrement égal au nombre d'enregistrement de ma table1 + celui de ma table2.

Je sais pas si y a un moyen plus propre pour gérer ce genre d'architecture...

[fsmrel]

Le problème avec ton modèle c'est qu'on perd la hiérarchie des données

Qu’entendez-vous par perdre la hiérarchie des données ?
Je ne sais pas si votre SGBD traite de la jointure récursive, mais selon la norme SQL, DB2, ou encore SQL Server, il n’y aucun problème.
Je reprends ici le jeu d’instructions que je vous ai déjà proposé :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Create table MaTable (  
    TableId     Integer      Not Null
  , TableNom    Varchar(48)  Not Null 
 , Constraint T1 Primary Key (TableId)
) ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Create table Hierarchie (
    TableId     Integer      Not Null
  , ParentId    Integer      Not Null
 , Constraint H1 Primary Key (TableId)
 , Constraint H2 Foreign Key (TableId) References MaTable
        On delete Cascade
 , Constraint H3 Foreign Key (ParentId) References MaTable
        On delete No Action
 , Constraint H4 Check (ParentId <> TableId)
) ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Insert Into MaTable Values (1, 'table 1') 
Insert Into MaTable Values (2, 'table 2') 
Insert Into MaTable Values (3, 'table 3') 
Insert Into MaTable Values (4, 'table 4') 
Insert Into MaTable Values (5, 'table 5') 
Insert Into MaTable Values (6, 'table 6') 
Insert Into MaTable Values (7, 'table 7') 
Insert Into MaTable Values (8, 'table 8')

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Insert Into Hierarchie Values (2, 1)
Insert Into Hierarchie Values (3, 2)
Insert Into Hierarchie Values (4, 2)
Insert Into Hierarchie Values (5, 3)
Insert Into Hierarchie Values (6, 5)
Insert Into Hierarchie Values (7, 5)

Pour retrouver la descendance de la table pour laquelle ParentId vaut i (i = 1 dans l'exemple ci-dessous) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
With Vue (Descendante, Ascendante) As 
    (
     Select TableId, ParentId 
     From   Hierarchie  
     Where  ParentId = 1  
    UNION ALL 
     Select I.TableId, I.ParentId  
     From   Vue, Hierarchie As I 
     Where  I.ParentId = Vue.Descendante 
    )
 Select Ascendante, Descendante From Vue 
;

Résultat des opérations :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Ascendante   Descendante
    1             2
    2             3
    2             4
    3             5
    5             6
    5             7

Ce résultat ne ressemble-t-il pas à une hiérarchie ? Qu’a-t-on perdu ?

[fsmrel]

Je réfléchis en terme de perf (pour faire les jointures) sachant que j'ai une dizaine de tables contenant chacune des dizaines de milliers de lignes....

Concernant Oracle, la jointure récursive est traitée sous une forme différente(clause Connect by), mais j'ai observé une performance excellente (100 millisecondes pour extraire une nomenclature, dans une table de 500000 lignes), dans un secteur où l’on ne rigole pas avec les nomenclatures, à savoir celui de la grande distribution.

Et bien sûr avec la structure que je vous ai proposée et qui est un classique depuis bien des années.

Rien ne vous interdit de faire des tests à votre tour.