Discussion :

[clavier12AZQSWX]

bonjour,

j'ai besoin d'un ptit conseil pour structurer mes informations de catégorie, sachant qu'une catégorie peut être un sous-ensemble de catégorie, lui-même un sous-sous-ensemble de catégorie.

Auourd'hui je veux structurer 3 niveaux seulement.

voilà j'héiste entre 2 schémas et j'aimerai savoir à long terme (avec bcp de données dedans, genre des milliers) ce qui sera le plus rapide à traiter pour un usage classique (bcp de select, bcp de recherche).


choix 1 (tout dans le même panier):

id_cat
nom_cat
id_sub_cat

choix 2 (une table par niveau):

id_cat
nom_cat
id_sub1_cat

id_sub1_cat
nom_cat
id_sub2_cat

id_sub2_cat
nom_cat

alors, qu'en pensez-vous ?

qu'est-ce qui à termes, va plonbler les performances du sgbd (sera mysql ou postgres) ?

[CinePhil]

Le premier schéma est meilleur mais posera des problème à l'utilisation sous MySQL car il faut utiliser des requêtes récursives pour "déplier" l'arbre des catégories, ce que ne connaît pas MySQL.
Ceci dit, si tu n'as que 3 niveaux, une double auto-jointure fera l'affaire et ne devrait aps être trop pénalisante. Par contre, si la profondeur de l'arbre est appelée à augmenter, ça complexifiera les requêtes.

Avec Postgresql ou un autre SGBD implémentant plus fidèlement la norme SQL par contre, je pense que ça ne posera pas de problème.

Le second schéma est moins bon du point de vue conceptuel. Le désavantage de ce modèle aussi est qu'il impose une modification de la structure des données si on veut ajouter plus tard un niveau, ainsi que des requêtes et des vues qui redonnent l'arbre à l'utilisateur. C'est moins maintenable.

Tu peux aussi regarder la structure en arbre intervallaire mais pour seulement 3 niveaux, ça me semble un peu trop riche et c'est plus complexe à mettre en oeuvre.

[fsmrel]

Bonsoir,

Auourd'hui je veux structurer 3 niveaux seulement.

Et demain, quatre ? N ?

On va faire comme si. Supposons qu’une sous-catégorie ne rentre dans la composition que d’une seule catégorie.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Catégorie composée   Catégorie composante d’une catégorie
---------------------------------------------------------
c1                   c11
c1                   c12
c1                   c13
c11                  c111
c11                  c112
c12                  c121
c12                  c122
c12                  c123
c2                   c21
c2                   c22
...                  ...

Vous pourriez modéliser la chose ainsi :




Traduction SQL

Table des catégories, sous-catégories, sous-sous-catégories, sous-...-sous-catégories :

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE CATEGORIE 
(
        CategId     VARCHAR(8)    NOT NULL
      , CategNom    VARCHAR(64)   NOT NULL
    , CONSTRAINT CATEGORIE_PK PRIMARY KEY (CategId)
 ) ;

N.B. Normalement, la colonne CategId est du type INT, si j’utilise VARCHAR c’est pour des raisons de confort ponctuel.


Tables des composants/composés :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE COMPOSITION 
(
       CategId     VARCHAR(8)    NOT NULL
    ,  ComposeId   VARCHAR(8)    NOT NULL
    , CONSTRAINT COMPOSITION_PK PRIMARY KEY (CategId)
    , CONSTRAINT COMPOSITION_COMPOSANT_FK FOREIGN KEY (CategId)
                 REFERENCES CATEGORIE (CategId)
    , CONSTRAINT COMPOSITION_COMPOSEE_FK FOREIGN KEY (ComposeId)
                 REFERENCES CATEGORIE (CategId)
) ;

Un début de jeu d’essai. Catégories :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c1', 'catégorie 1') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c11', 'catégorie 11') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c12', 'catégorie 12') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c13', 'catégorie 13') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c111', 'catégorie 111') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c112', 'catégorie 112') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c121', 'catégorie 121') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c122', 'catégorie 122') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c123', 'catégorie 123') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c2', 'catégorie 2') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c21', 'catégorie 21') ; 
INSERT INTO CATEGORIE (CategId, CategNom) VALUES ('c22', 'catégorie 22') ;

Composants/composés :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c1', 'c11') ;
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c1', 'c12') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c1', 'c13') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c11', 'c111') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c11', 'c112') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c12', 'c121') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c12', 'c122') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c12', 'c123') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c2', 'c21') ; 
INSERT INTO COMPOSITION (ComposeId, CategId) VALUES ('c2', 'c22') ;

Sauf si vous utilisez MySQL, vous pouvez bâtir une requête correspondant à une jointure récursive et la loger dans une vue :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW VUE_RECURSIVE
AS
WITH COMPOSITION_VUE (Composé, Composant, Liste, Profondeur) AS
       ((SELECT ComposeId, CategId, CAST(ComposeId + ', ' + CategId AS VARCHAR(MAX)), 1
         FROM   COMPOSITION)
         UNION ALL
        (SELECT y.ComposeId, y.CategId, CAST(x.Liste + ', ' + y.CategId AS VARCHAR(MAX)), Profondeur + 1
         FROM   COMPOSITION_VUE AS x JOIN COMPOSITION AS y
              ON x.Composant = y.ComposeId))
SELECT DISTINCT Composé, Composant, Liste, Profondeur
FROM   COMPOSITION_VUE ;

La colonne Liste permet de produire la descendance (si elle en a) de chaque catégorie, sous la forme :

Catégorie, Sous-catégorie, Sous-sous-catégorie, etc.

La colonne Profondeur permet de connaître le nombre de générations des descendants de la catégorie.

Vous pouvez ensuite exploiter cette vue à votre guise pour en extraire ce qui vous convient.


A l’occasion, vous pouvez parcourir la discussion sur les « arbres entremêlés ».

[clavier12AZQSWX]

tien c'est marrant (étonnant je veux dire), vous apportez une troisième solution.


en fait j'ai oublié de dire qu'une catégorie ne peut avoir qu'une seule catégorie mère.

dans votre cas, ça suppose qu'on peut faire une composition, une formule or je n'ai pas besoin de cela.
disons que je veux faire une filliation masculine.
X est le père de Y qui lui-même est le père de Z.

merci pour le code de la vue, je n'avais jamais vu cette syntaxe (with) utilisée comme ça.

[fsmrel]

une catégorie ne peut avoir qu'une seule catégorie mère.

Si vous prêtez attention à la représentation graphique que je vous ai proposée, vous observerez (en remplaçant la notion de composition par celle de parenté) qu’elle correspond précisément à cette contrainte :

Une catégorie peut avoir au plus une catégorie pour parente et une catégorie peut être parente de plusieurs catégories.

disons que je veux faire une filliation masculine.
X est le père de Y qui lui-même est le père de Z.

On va aménager la représentation graphique en fonction de votre remarque, en continuant à supposer qu’un père peut avoir plus d’un fils (dans nos sociétés ça n’est pas exceptionnel) :

[clavier12AZQSWX]

On va aménager la représentation graphique en fonction de votre remarque, en continuant à supposer qu’un père peut avoir plus d’un fils (dans nos sociétés ça n’est pas exceptionnel)

mais je ne suis pas intéressé par la filliation père-->fils.

savoir qui est le père d'un fils suffit. savoir quel(s) fils a un père ne m'intéresse pas (pas besoin en fait)

c'est pour ça que je pensais à une solution à 1 tables et même une relation plutôt que d'avoir à créer une autre relation "filiation"

[fsmrel]

Qui peut le plus peut le moins. Même en n’utilisant qu’une seule table, vous pourrez répondre à la question « Quelle est la descendance de X ? » Cela tient à la nature même du Modèle Relationnel de Données (dont SQL se réclame) qui ne fonctionne pas par pointeurs contrairement aux systèmes pré-relationnels, mais par association de valeurs.

Si on transforme ainsi la représentation graphique précédente :



Alors non seulement vous saurez là aussi retrouver l’ascendance d’une catégorie, mais aussi sa descendance.

Structure SQL correspondante de la table CATEGORIE :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE CATEGORIE 
(
        CategId     VARCHAR(8)    NOT NULL
      , CategNom    VARCHAR(64)   NOT NULL
      , ParentId    VARCHAR(8)
    , CONSTRAINT CATEGORIE_PK PRIMARY KEY (CategId)
    , CONSTRAINT CATEGORIE_FK FOREIGN KEY (ParentId)
                 REFERENCES CATEGORIE (CategId)
 ) ;

Veuillez noter la présence de la contrainte référentielle CATEGORIE_FK, sinon la colonne ParentId peut contenir n’importe quoi. Par ailleurs, si c111 a pour parent c11 qui a pour parent c1 qui n’a pas de parent, alors l’absence de parent doit faire l’objet d’une marque Null. Le bonhomme Null montre donc le bout de son nez, ce qui n’est jamais bon, d’où la recommandation : « Pas de table qui s’auto-référence ». Pour ma part, j’utilise systématiquement la paire de tables CATEGORIE, FILIATION, comme dans mon message précédent.

Cela dit, en surveillant de près le bonhomme Null, adaptons le jeu d’essai à la nouvelle structure de la table CATEGORIE :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c1', 'catégorie 1', NULL) ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c11', 'catégorie 11', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c12', 'catégorie 12', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c13', 'catégorie 13', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c111', 'catégorie 111', 'c11') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c112', 'catégorie 112', 'c11') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c121', 'catégorie 121', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c122', 'catégorie 122', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c123', 'catégorie 123', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c2', 'catégorie 2', NULL) ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c21', 'catégorie 21', 'c2') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c22', 'catégorie 22', 'c2') ;

Voici un exemple de recherche de l’ascendance des catégories :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 WITH FILIATION (Enfant, NomEnfant, Parent, Filiation, Profondeur) AS
       ((SELECT CategId, CategNom, ParentId, CAST(CategId + ', ' + ParentId AS VARCHAR(MAX)), 1
         FROM   CATEGORIE)
         UNION ALL
        (SELECT y.CategId, y.CategNom, ParentId, CAST(y.CategId + ', ' + x.Filiation AS VARCHAR(MAX)), Profondeur + 1
         FROM   FILIATION AS x JOIN CATEGORIE AS y ON x.Enfant = y.ParentId
         WHERE  Parent IS NOT NULL))
    SELECT DISTINCT x.Enfant, NomEnfant, COALESCE(Filiation, 'sans') AS Ascendance
    FROM   FILIATION AS x JOIN  
           (SELECT DISTINCT Enfant, Max(Profondeur) AS Profondeur
            FROM   FILIATION 
            GROUP BY Enfant) AS y
            ON x.Enfant = y.Enfant AND x.Profondeur = y.Profondeur ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Enfant    NomEnfant        Ascendance
-----------------------------------------
c1        catégorie 1      sans
c11       catégorie 11     c11, c1
c111      catégorie 111    c111, c11, c1
c112      catégorie 112    c112, c11, c1
c12       catégorie 12     c12, c1
c121      catégorie 121    c121, c12, c1
c122      catégorie 122    c122, c12, c1
c123      catégorie 123    c123, c12, c1
c13       catégorie 13     c13, c1
c2        catégorie 2      sans
c21       catégorie 21     c21, c2
c22       catégorie 22     c22, c2

Se rapproche-t-on de ce que vous recherchez ?

[CinePhil]

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE CATEGORIE 
(
        CategId     VARCHAR(8)    NOT NULL
      , CategNom    VARCHAR(64)   NOT NULL
      , ParentId    VARCHAR(8)
    , CONSTRAINT CATEGORIE_PK PRIMARY KEY (CategId)
    , CONSTRAINT CATEGORIE_FK FOREIGN KEY (ParentId)
                 REFERENCES CATEGORIE (CategId)
 ) ;

Oh ! Tu autorises une clé étrangère nulle François ?

[fsmrel]

Je l’attendais... J’ai donc fait figurer ci-dessus une solution peccamineuse qui aura vraisemblablement la faveur des suffrages et ne fera pas partir le lecteur en courant. Mais par ailleurs j’ai écrit :

Le bonhomme Null montre donc le bout de son nez, ce qui n’est jamais bon, d’où la recommandation : « Pas de table qui s’auto-référence ». Pour ma part, j’utilise systématiquement la paire de tables CATEGORIE, FILIATION, comme dans mon message précédent.

Aussi je me dois de fournir la requête mettant en jeu ces deux tables et avec laquelle Null est hors-la-loi. Si l’on ne cherche pas à récupérer les catégories sans ascendance (c1 et c2 dans l’exemple) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
WITH FILIATION (Enfant, Parent, Ascendance, Profondeur) AS
     ((SELECT CategId, ComposeId, CAST(CategId + ', ' + ComposeId AS VARCHAR(MAX)), 1
       FROM   COMPOSITION)
       UNION ALL
      (SELECT y.CategId, y.ComposeId, CAST(y.CategId + ', ' + x.Ascendance AS VARCHAR(MAX)), Profondeur + 1
       FROM   FILIATION AS x JOIN COMPOSITION AS y ON x.Enfant = y.ComposeId))
    SELECT DISTINCT x.Enfant, x.Ascendance
    FROM   FILIATION AS x JOIN  
           (SELECT DISTINCT Enfant, Max(Profondeur) AS Profondeur
            FROM   FILIATION 
            GROUP BY Enfant) AS y
            ON x.Enfant = y.Enfant AND x.Profondeur = y.Profondeur ;

Si l’on veut aussi les catégories sans ascendance (comme dans le cas de la requête peccamineuse) :

Code SQL :

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
WITH FILIATION (Enfant, Parent, Ascendance, Profondeur) AS
     ((SELECT CategId, ComposeId, CAST(CategId + ', ' + ComposeId AS VARCHAR(MAX)), 1
       FROM   COMPOSITION)
       UNION ALL
      (SELECT y.CategId, y.ComposeId, CAST(y.CategId + ', ' + x.Ascendance AS VARCHAR(MAX)), Profondeur + 1
       FROM   FILIATION AS x JOIN COMPOSITION AS y ON x.Enfant = y.ComposeId))
    SELECT DISTINCT x.Enfant, x.Ascendance
    FROM   FILIATION AS x JOIN  
           (SELECT DISTINCT Enfant, Max(Profondeur) AS Profondeur
            FROM   FILIATION 
            GROUP BY Enfant) AS y
            ON x.Enfant = y.Enfant AND x.Profondeur = y.Profondeur 
    UNION
      SELECT CategId, 'sans' FROM   CATEGORIE
    EXCEPT
      SELECT CategId, 'sans' FROM   COMPOSITION ;

Is it good enough, Tovaritch ?

[clavier12AZQSWX]

moi aussi, en tant que développeur et concepteur j'ai souvant été embetté par les valeurs nulles relationnelles.

pour remédier à cela, c'est toujours considéré que chauqe premier tuples (insérés manuellement en amont) qui aura un id de 1 sera la valeur par défaut de tout (null, suppression, NC, N/A ..Etc).
Et ainsi, non seulement j'ai plus ces soucis de null, mais en plus je n'ai plus ces souciens de svoir si quand je reçois une valeur "1", si c'est int(1) ou un booléan.


et même pour l'utilisateur final (celui devant son écran), voir par dans une liste déroulante une valeur (NC N/A ou autre libellé) plutôt que vide c'est rassurant car explicite et visuel.

Et je dis pas la simplicité du développement à ne plus avoir à gérer une liste déroulante sans savoir si il aura ou pas une valeur sélectionnée ou pas ou null.

[fsmrel]

j'ai souvent été embêté par les valeurs nulles relationnelles.

Que le bonhomme Null vous ait embêté, je n’en doute pas. Toutefois deux remarques :

1) Null n’est pas une valeur, mais disons une marque (ou un marqueur).

2) Null est une invention des pères de SQL, il n’a pas d’existence en relationnel (c'est-à-dire dans la théorie relationnelle alias Modèle Relationnel de Données).


Pour en revenir à la solution de la table unique, si l’on vous suit, la structure de la table CATEGORIE devient celle-ci :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE CATEGORIE 
(
        CategId     VARCHAR(8)    NOT NULL
      , CategNom    VARCHAR(64)   NOT NULL
      , ParentId    VARCHAR(8)    NOT NULL
    , CONSTRAINT CATEGORIE_PK PRIMARY KEY (CategId)
    , CONSTRAINT CATEGORIE_FK FOREIGN KEY (ParentId)
                 REFERENCES CATEGORIE (CategId)
 ) ;

Structure dans laquelle la colonne ParentId ne peut pas être investie par le bonhomme Null : CinePhil appréciera.

En conséquence de l’évolution de la structure, le jeu d’essai devient le suivant :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('1', 'catégorie 1', '1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c1', 'catégorie 1', '1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c11', 'catégorie 11', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c12', 'catégorie 12', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c13', 'catégorie 13', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c111', 'catégorie 111', 'c11') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c112', 'catégorie 112', 'c11') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c121', 'catégorie 121', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c122', 'catégorie 122', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c123', 'catégorie 123', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c2', 'catégorie 2', '1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c21', 'catégorie 21', 'c2') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c22', 'catégorie 22', 'c2') ;

Où '1' représente la valeur spéciale « qui sert à tout » (colonnes CategId, ParentId).
Ainsi, la 1ère ligne a pour valeur <'1', 'catégorie 1', '1'>, où la colonne ParentId fait référence à la colonne CategId : '1' fait référence à '1', réflexivité et intégrité référentielle obligent. Attention à surveiller le déclenchement des boucles infinies dans le cas des requêtes récursives comme ici :

Boucle infinie non contrôlée :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH FILIATION (Enfant, NomEnfant, Parent, Ascendance, Profondeur) AS
       ((SELECT CategId, CategNom, ParentId, CAST(CategId + ', ' + ParentId AS VARCHAR(MAX)), 1
         FROM   CATEGORIE)
         UNION ALL
        (SELECT y.CategId, y.CategNom, ParentId, CAST(y.CategId + ', ' + x.Ascendance AS VARCHAR(MAX)), Profondeur + 1
         FROM   FILIATION AS x JOIN CATEGORIE AS y ON x.Enfant = y.ParentId
       ))
    SELECT DISTINCT x.Enfant, NomEnfant, Ascendance
    FROM   FILIATION AS x JOIN  
           (SELECT DISTINCT Enfant, Max(Profondeur) AS Profondeur
            FROM   FILIATION 
            GROUP BY Enfant) AS y
            ON x.Enfant = y.Enfant AND x.Profondeur = y.Profondeur ;

Boucle infinie contrôlée (cf. « WHERE Parent <> '1' ») :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH FILIATION (Enfant, NomEnfant, Parent, Ascendance, Profondeur) AS
       ((SELECT CategId, CategNom, ParentId, CAST(CategId + ', ' + ParentId AS VARCHAR(MAX)), 1
         FROM   CATEGORIE)
         UNION ALL
        (SELECT y.CategId, y.CategNom, ParentId, CAST(y.CategId + ', ' + x.Ascendance AS VARCHAR(MAX)), Profondeur + 1
         FROM   FILIATION AS x JOIN CATEGORIE AS y ON x.Enfant = y.ParentId
         WHERE  Parent <> '1'         -- Eviter les boucles infinies
       ))
    SELECT DISTINCT x.Enfant, NomEnfant, Ascendance
    FROM   FILIATION AS x JOIN  
           (SELECT DISTINCT Enfant, Max(Profondeur) AS Profondeur
            FROM   FILIATION 
            GROUP BY Enfant) AS y
            ON x.Enfant = y.Enfant AND x.Profondeur = y.Profondeur ;

Mais on peut très bien en passer par une solution qui ne contraigne pas à considérer '1' comme valeur spéciale : les catégories qui n’ont pas de catégorie parente ('c1' et 'c2' dans l’exemple) peuvent s’auto-référencer :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c1', 'catégorie 1', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c11', 'catégorie 11', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c12', 'catégorie 12', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c13', 'catégorie 13', 'c1') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c111', 'catégorie 111', 'c11') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c112', 'catégorie 112', 'c11') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c121', 'catégorie 121', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c122', 'catégorie 122', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c123', 'catégorie 123', 'c12') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c2', 'catégorie 2', 'c2') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c21', 'catégorie 21', 'c2') ; 
INSERT INTO CATEGORIE (CategId, CategNom, ParentId) VALUES ('c22', 'catégorie 22', 'c2') ;

Le contrôle des boucles infinies impose cette fois-ci de coder « WHERE Parent <> Enfant » :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH FILIATION (Enfant, NomEnfant, Parent, Ascendance, Profondeur) AS
       ((SELECT CategId, CategNom, ParentId, CAST(CategId + ', ' + ParentId AS VARCHAR(MAX)), 1
         FROM   CATEGORIE)
         UNION ALL
        (SELECT y.CategId, y.CategNom, ParentId, CAST(y.CategId + ', ' + x.Ascendance AS VARCHAR(MAX)), Profondeur + 1
         FROM   FILIATION AS x JOIN CATEGORIE AS y ON x.Enfant = y.ParentId
         WHERE  Parent <> Enfant         -- Eviter les boucles infinies
       ))
    SELECT DISTINCT x.Enfant, NomEnfant, Ascendance 
    FROM   FILIATION AS x JOIN  
           (SELECT DISTINCT Enfant, Max(Profondeur) AS Profondeur
            FROM   FILIATION 
            GROUP BY Enfant) AS y
            ON x.Enfant = y.Enfant AND x.Profondeur = y.Profondeur ;

Pour l’anecdote, l’auto-référence utilisée dans cet exemple revient à dire qu’un PDG ('c1') et son premier collaborateur ('c11') sont hiérarchiquement au même niveau, puisque rattachés tous les deux à 'c1', ce qui est discutable...

pour l'utilisateur final (celui devant son écran), voir par dans une liste déroulante une valeur (NC N/A ou autre libellé) plutôt que vide c'est rassurant car explicite et visuel.

Attention, c’est à vous de présenter les données à l’utilisateur et de remplacer ce que vous appelez « vide » (Null ?) par « sans objet », « inconnu » etc.