Les formes relationnelles normales (un à un – un à plusieurs – plusieurs à plusieurs) ne peuvent répondre à toutes les problématiques.
C'est le cas lors de la mise en place d'un système de liste complexe à plusieurs niveaux comme un organigramme, une classification quelconque ou un système de suivi de messages (commentaires – discussions – courriels – forums).
Mettre en place un système hiérarchique
Utiliser des relations un à plusieurs
Un tel schéma relationnel n'est absolument pas adapté.
Création des tables
À chaque niveau hiérarchique correspond une table. Si j'ai une profondeur de 10 niveaux, il faudra créer 10 tables.
Et si d'aventure, j'ai besoin d'un niveau supplémentaire, ma seule alternative est de créer une nouvelle table et la relier à la table de niveau supérieur.
Les choses se compliquent si ce niveau doit s'intercaler entre deux niveaux existant. Dans ce cas, il faut détruire les relations existantes et les recréer avec la nouvelle table.
Les données manquent de cohérence. On voit que la table T_BUDGET contient comme données le nom des tables de niveau inférieur (ici T_RECETTE et T_DEPENSE), alors que d’autres des données pures comme la table T_PLACEMENT.
Les relations un à plusieurs et plusieurs à plusieurs sont inexploitables. Récupérer une branche complète comme l’ensemble des dépenses devient une gageure et une simple requête n’y suffira pas.
La consultation de l’information n’est pas plus simple et l’on va se retrouver avec un nombre important de formulaires.
En conclusion, les formes normales relationnelles ne peuvent répondre à notre demande.
Utiliser une relation réflexive
Dans un précédent article, j'ai abordé les relations réflexives qui permettent de réaliser un système hiérarchique à deux niveaux à l'aide d'une seule table.
Retrouver les enfants directs ne pose aucun problème particulier et une requête sélection répond à la demande.
J’affiche bien les enfants directs correspondant au critère.
Même si elles sont utiles, elles montrent vite des limites dès lors qu'il faut gérer des arborescences à plus d’un niveau.
Si je souhaite consulter outre les enfants directs, les enfants des enfants, les choses se compliquent.
La seule alternative est d'appliquer un traitement récursif sur les données, chose que Microsoft Access ne sait pas réaliser et je n’ai d’autre choix que de le faire par programmation.
Notation intervallaire
Principe de base
Nous n’aurons qu’une seule table et chaque donnée sera numérotée selon certaines règles.
On va donner deux indices pour chaque donnée.
- Un pour le bord gauche de la donnée.
- Un pour le bord droit de la donnée.
Cela se traduit par la structure de table suivante.
Ce qui donne en mode consultation les données suivantes.
Cette forme relationnelle n’est bien sûr pas spécifique à Microsoft Access et peut être adapté à tout système de bases de données relationnelles.
Dans cet article j’utiliserais l’outil requête spécifique de Microsoft Access. Il est possible de voir le code SQL des requêtes à l’aide du mode dédié.
Peu voire aucune modification ne sera à effectuer avec un autre gestionnaire SQL.
Numérotation
Bord gauche
Ma racine (ici la donnée « Budget ») commence à zéro (0), mais je pourrais débuter avec une autre valeur au cas où je souhaite plus tard rajouter des données au-dessus de la racine.
Ensuite je traite le premier enfant en incrémentant le bord gauche précédent de 1. Ainsi le bord gauche de la donnée « Recette » prend la valeur 1 puisque celle de « Budget » est égale à zéro.
Je passe ensuite au premier enfant de cet enfant soit « Salaire » et j’incrément de nouveau de 1. Le bord gauche de la donnée « Salaire » prend l’indice 2.
Je répète ce processus sur tous les enfants.
Bord droit
Quand une donnée n’a pas d’enfant, je numérote son bord droit.
C’est son bord gauche + 1.
Dans l’exemple, la donnée « Salaire » n’a pas d’enfant le bord droit prend la valeur 3 (bord gauche = 2 + 1).
Rupture
Quand une donnée s’est vu attribuée un indice sur son bord droit, deux cas se présentent.
Il existe une donnée de même niveau
Le bord gauche de la donnée suivante prend la valeur du bord droit précédent plus un.
La donnée « Placement » est au même niveau que « Salaire ». Son bord gauche prend la valeur du bord droit de « Salaire » + 1 soit l’indice 4.
Toutes les données de même niveau ont été numérotées à droite
Dans ce cas, il faut numéroter les bords droits des données de niveau supérieur.
Chaque fois que l’on remonte d’un niveau, on incrémente le bord droit de la donnée.
Quand le bord droit de la donnée « Ass. Vie » est renseignée, l’indice droit de « Placement » est incrémenté.
De la même manière le bord droit de « Recettes » est à son tour incrémenté par rapport au bord droit de « Placement ».
On recommence le même principe pour l’ensemble des branches.
Le bord droit de « Recettes » étant renseigné, le bord gauche de « Dépenses » qui est au même niveau prend la valeur 12 + 1.
Conséquences
Le bord droit de la donnée est toujours supérieur au bord gauche.
Le bord droit d’un niveau est toujours supérieur au bord droit du dernier de ses enfants.
Le bord droit de la donnée de premier niveau est l’indice le plus élevé de la hiérarchie.
Terminologie
Feuille
Quand le bord droit d’une valeur moins le bord gauche de la même valeur est égal à 1, on appelle la donnée une feuille
Les données « Salaires », « PEL », « Livret » et « Ass. Vie » sont des feuilles.
Nœud
Quand le bord droit d’une valeur moins le bord gauche de la même valeur est supérieur à 1, on dit que la donnée est englobante.
On appelle cette donnée un nœud ou sous-branche.
C’est le cas des données « Placements », « Recettes », « Budget ».
Bord droit d’un nœud
Le bord droit d’un nœud (et pas d’une feuille) c’est toujours la valeur de son bord gauche plus le double du nombre de ses enfants plus un.
La donnée « Placement » possède l’indice 4 pour bord gauche. Elle possède 3 enfants soit la valeur 6.
Le bord droit est donc 4 + 6 +1 = 11.
Pour « Recettes », nous avons 5 enfants soit la valeur 10 augmenté de la valeur du bord gauche ici 1 incrémenté de 1.
Ce qui nous donne bien la valeur 12.
Donc pour identifier un nœud, bord droit moins bord gauche est supérieur à 1.
Arbre
C’est l’ensemble de la structure composée de ses branches et feuilles.
Racine
C’est l’élément au plus haut niveau de la hiérarchie soit « Budget ».
Intervalle
On parle de représentation intervallaire car l’ensemble d’une branche est comprise dans la fourchette des bords gauche et droit du parent.
Mise en œuvre
Si à priori le principe semble complexe, nous allons voir que sa réalisation est plutôt simple.
Indexer les bords gauche et droit
Dans la suite de l’article, nous verrons que nous ne manipulerons jamais les bornes de l’arbre à la main mais à l’aide de requêtes.
Néanmoins, il est utile d’indexer les champs sans doublons.
C’est une protection utile car les doublons doivent être interdits et ces deux champs serviront souvent de recherches et tris.
Il est aussi utile de définir un index unique sur les deux bornes.
Consulter un intervalle
Lire une branche
Il suffit de déterminer les critères de l’indice minimal à gauche et maximal du bord droit pour récupérer l’ensemble des données comprises dans cet intervalle.
Dans la requête je demande l’ensemble de la branche « recettes ».
Si je ne souhaite pas reprendre la donnée parente « Recettes », il faut enlever l’opérateur égal dans les critères.
Compter les enfants
Ce n’est guère plus compliqué et une expression « Compte » fait l’affaire.
Ici je ne souhaite pas inclure le parent dans le comptage, donc je ne mets pas l’opérateur égal.
Consulter uniquement les feuilles d’un nœud
La requête et pratiquement identique au listage des enfants d’un nœud. Je rajoute dans les critères l’écart entre le bord droit et gauche qui doit être je rappelle à 1.
Contrairement au listage des enfants, la donnée « Placement » qui est un nœud n’apparait pas.
Consulter les feuilles à partir du libellé
Il n’est pas toujours simple de filtrer à l’aide de valeurs numériques et il est souvent plus simple de filtrer depuis le libellé de l’élément.
La démarche s’avère un peu plus complexe.
Tout d’abord nous allons ajouter deux fois la table « T_BUDGET » dans la requête.
Ensuite nous définissons un alias sur les tables (voir les propriétés de la table). J’ai choisi l’alias « NŒUD » pour la première et « PARENT » pour la seconde.
Je récupère bien tous les éléments sous la branche « Placement ».
- PEL.
- Livret.
- Ass. Vie.
Pour rajouter la mention « Placement », il suffit de supprimer l’expression +1 dans le critère du bord gauche.
Consulter uniquement les nœuds
Les requêtes sont identiques que pour les feuilles à ceci près que l’écart entre le bord droit et le bord gauche est supérieur à 1.
Lire un élément
Rien de plus simple puisqu’il suffit de donner la valeur du bord gauche.
Sans surprise la requête ressort l’élément « Vacances ».
Sélectionner les parents
On donne les bords gauche et droit de l’élément dont on souhaite voir les parents.
Je récupère bien toute la hiérarchie de la donnée « PEL ».
Consulter les parents à partir du libellé
La démarche est proche de la consultation des feuilles par le libellé.
Je récupère les parents de la feuille « PEL ».
- Budget.
- Recettes.
- Placement.
Si je souhaite ajouter la feuille « PEL » dans le résultat, comme précédemment, je supprime l’expression + 1 dans le critère du bord gauche.
Compter le nombre de nœuds
Les nœuds sont les données qui contiennent au moins un enfant. Plus précisément, ce sont les données dont l’écart entre le bord droit et le bord gauche est supérieur à 1.
La requête affiche 11 nœuds.
Ajouter des données
Je souhaite rajouter une feuille sous la feuille « Sport ».
L’insertion de données nécessite trois requêtes.
Mise à jour des bornes droites
Il faut incrémenter le bord droit des éléments supérieurs à l’emplacement de l’insertion pour faire la place pour le nouvel élément.
Il est aussi nécessaire d’incrémenter le bord droit du parent pour que la nouvelle feuille soit englobée dans la branche.
Il serait possible de commencer par le bord gauche et ensuite manipuler le bord droit. Dans les faits surtout avec des champs indexés il est préférable de commencer par le bord droit.
Je vais utiliser une requête mise à jour.
Le bord droit de la feuille « Sport » possède l’indice 42.
Mise à jour des bornes gauches
Ensuite on fait la même chose pour les bords gauche.
Nous avons maintenant un « trou » dans les indices où nous pouvons insérer la nouvelle donnée.
Insérer la donnée
Enfin on insère la nouvelle feuille dans l’emplacement libre.
Supprimer des données
Le principe reste le même que pour l’insertion à la différence près que l’opération se fait à l’inverse de l’insertion.
Supprimer la feuille
Cette opération ne nécessite aucune remarque particulière et nous utilisons une simple requête suppression.
Je supprime la nouvelle feuille « Abonnement salle ».
Mise à jour des bornes gauche
Il est préférable de ne pas laisser de « trous » dans l’arborescence.
Je vais donc décrémenter les bords gauche de 2.
Mise à jour des bornes de droite
Je réalise la même opération pour le bord droit.
Supprimer une branche
La démarche est identique à la suppression d’une feuille.
C’est le critère utilisé qui fait la différence. On donne le bord gauche et le bord droit de l’élément de plus haut niveau.
Dans cet exemple, les critères pour bord gauche et droit sont ceux de la branche « Placement ».
Modifier les bornes gauches
La principale difficulté réside dans le calcul de l’écart à prendre en compte pour renuméroter les bords.
C’est la distance entre le bord droit – le bord gauche + 1. Ce qui dans cet exemple donne le résultat : 11 – 4 + 1 = 8.
Modifier les bornes droites
On répète l’opération pour le bord droit.
Niveau de l’arbre
Il peut être intéressant de connaître le niveau d’une donnée dans la hiérarchie.
Là encore ma requête contient deux fois la table « T_BUDGET » avec pour alias « NŒUD » et « PARENT ».
Voici le résultat généré par la requête.
Pour commencer la numérotation à 1 plutôt que 0, supprimer dans la fonction « Compte », l’expression – 1.
Niveau d’un élément
En utilisant la même requête, je défini le critère pour le bord gauche.
Ma requête affiche l’élément « Loyer ».
Niveau d’une branche
Toujours à l’aide de la même requête, je rajoute le champ « BUDGET_DROITE » et donne le critère de la borne supérieure.
J’affiche le niveau pour l’ensemble de la branche « Placement ».
Indenter les éléments de liste
Pour une meilleure visibilité, il est possible d’indenter les valeurs de la liste selon le niveau de l’élément.
C’est le champ calculé « Liste » qui se charge du travail.
Travailler avec une table liée
Imaginons une table « T_OPERATION » reliée à la table « T_BUDGET ».
Voici le contenu de cette table.
Compter le nombre d’opérations par budget
Rien de nouveau dans cette requête.
Le dernier critère n’affiche que le groupement par feuilles.
Si l’on souhaite avoir aussi le compte pour les nœuds, il faut supprimer le dernier critère de la requête.
Nous trouvons bien le nombre d’opérations par catégorie de budget.
Faire la somme des opérations
C’est exactement la même requête, sauf que l’on choisit la fonction de regroupement « Somme » sur le champ « OPERATION_MONTANT ».
Nous connaissons maintenant le montant par catégorie de budget.
Exploiter la représentation intervallaire
Nous avons besoin de formulaires pour manipuler l’information.
Quelques particularités peuvent se présenter la représentation intervallaire n’étant pas naturelle pour Microsoft Access.
Formulaire de consultation
Il est constitué d’un formulaire principal et d’un sous-formulaire.
Formulaire principal
Il se compose d’une zone de liste qui permet de filtrer le sous-formulaire et de quelques champs reprenant les informations sur l’élément sélectionné.
Il est nommé « Consultation_fr ».
Liste de choix
Cliquer pour plier/déplier le tableau.
| Propriétés | Valeur |
|---|---|
| Nom | lstChoix |
| Nbre de colonnes | 5 |
| Largeur colonnes | 0cm;0cm;0cm;0cm |
| Colonne liée | 1 |
La propriété source ressemble à la requête d’indentation à la différence que les champs obligatoires sont rajoutés.
Zone d’information
Les différentes zones de texte reprennent les informations de la ligne sélectionnée dans la liste.
Cliquer pour plier/déplier le tableau.
| Nom de la zone | Source contrôle | Champ sous-jacent |
|---|---|---|
| txtID | =[lstChoix].[Column](0) | BUDGET_ID |
| txtGauche | =[lstChoix].[Column](1) | BUDGET_GAUCHE |
| txtDroite | =[lstChoix].[Column](2) | BUDGET_DROITE |
| txtLib | =[lstChoix].[Column](3) | BUDGET_LIB |
Sous-formulaire de consultation
Il s’appelle « Consultation_sfr1 ».
Sa propriété « Affichage par défaut » est fixée sur « Formulaires continus ».
Sa propriété source est une requête qui filtre le formulaire sur les bornes de l’élément de la liste de choix du formulaire parent.
Ce sous-formulaire est ensuite intégré dans le formulaire principal.
Rafraichir les données
Microsoft Access ne sait pas mettre à jour automatiquement le sous-formulaire quand je clique sur la liste.
Je vais donc le faire à l’aide d’une procédure sur l’évènement « Sur clic » de la liste (cliquer sur les 3 points pour choisir le générateur).
Private Sub lstChoix_Click()
Me.Refresh
End Sub
Cela revient à appuyer sur la touche [F9] qui permet d’actualiser un formulaire.
Ajouter / supprimer des éléments
Je ne verrais que la partie d’insertion d’élément dans l’arborescence.
La suppression ne pose aucun problème particulier. Les requêtes pour supprimer ont été abordées plus haut, et la démarche est identique à l’insertion.
Formulaire d’ajout
Le formulaire est indépendant et s’appelle « InsertDelete_fr ».
Liste de choix du parent
Le nouvel élément aura pour parent la donnée sélectionnée dans la liste.
La source de la liste est la table « T_BUDGET ».
Cliquer pour plier/déplier le tableau.
| Propriétés | Valeur |
|---|---|
| Nom | lstChoix |
| Nbre de colonnes | 4 |
| Largeur colonnes | 0cm;0cm;0cm |
| Colonne liée | 1 |
Zones de critères
Je récupère dans deux zones de texte, les indices des bords gauche et droit de l’élément sélectionné dans la liste comme vu précédemment.
Ce seront ces valeurs qui serviront de critère pour les requêtes.
Ils sont nommés « txtGauche » et « txtDroite ».
Zone de saisie
C’est la valeur existante dans cette zone de texte qui sera rajouté à la table.
Sa propriété « Nom » est fixée à « txtNewItem ».
Bouton de commande
Il est nommé « cmdAddItem ».
Création des requêtes
Pour ajouter les données, j’ai donc besoin de 3 requêtes comme je l’ai expliqué plus haut.
Pousser les bords droits
La requête a été détaillée plus avant. Je me contente de définir les critères avec les valeurs du formulaire.
Pousser les bords gauches
Insérer la donnée
C’est une simple requête ajout qui utilise les données du formulaire.
Écriture des macros
Comme ce n’est pas très ergonomique d’enchainer les 3 requêtes à la main, je vais le faire par macro.
Comme le préconise Microsoft, il serait plus efficace d’écrire du code VBA.
J’ai nommé cette macro « mac_ajout ».
Supprimer l’avertissement
Chaque fois que Microsoft Access exécute une requête action, il affiche un message de confirmation qui peut être gênant.
Pour modifier ce comportement, je rajoute une action « Avertissements actifs » fixé à non avant l’exécution de chaque requête.
Si cette action n’est pas visible dans la liste des actions disponibles, il faut sélectionner dans la barre d’outils des macros, l’icône « Afficher toutes les actions ».
Affecter la macro
J’associe la macro à l’évènement « clic » du bouton de commande du formulaire (clic sur la flèche).
Formulaire sur la table liée
Il n’y a rien de particulier ici, puisque c’est une simple relation de un à plusieurs.
La formulaire principal possède comme source la table « T_Budget » et le sous-formulaire la table « T_OPERATION ».
Les propriétés « Champ père » et « Champ fils », pointent sur le champ « BUDGET_ID ».
Le sous-formulaire est affiché en mode continu.
Conclusion
Même si par certains aspects la représentation intervallaire n’est pas simple à mettre en œuvre, elle est incontournable avec des hiérarchies complexes.
Télécharger l’exemple
Vous trouverez une base de données exemple au format Microsoft Access 2010 en suivant ce lien (format zip).
Achetez mon ouvrage !
Mon PDF « Créer un planning perpétuel sur Microsoft Excel sans macro » est disponible à la vente.
Pour plus d’informations, rendez-vous sur la page dédiée.