Souvent nous avons besoin d'un système hiérarchique, par exemple :

• projet => sous-projets ;
• contrats => avenants ;
• Etc.

Construction selon la norme 3NF

L’exemple classique trouvé sur le Net c’est l'organigramme de l'entreprise, je ne dérogerais pas à la règle.

J’ai des salariés qui sont des subordonnés et d’autres des supérieurs. Je souhaite conserver cette hiérarchie.

Utiliser du plusieurs à plusieurs

Pour se faire j’ai besoin de la liste des employés.

Le réflexe naturel serait de créer une seconde table qui associe l’employé en lui attribuant le rôle de supérieur (champ [T_ORG].[EMP_SUP]) ou subordonné (champ [T_ORG].[EMP_SUB]).

Nous constatons que « Pierre » d'index 1 est le supérieur de « Marie » et « Loïc » alors que « Marie » est la chef de « Marc » et « Anne ».
Nous voyons que "Pierre", n'apparaît pas dans le champ [EMP_SUB], il n'a pas de supérieur.

Pour simplifier l’affichage, les deux champs [EMP_SUP] et [EMP_SUB] sont renseignés avec une zone de liste.

Ce qui donne l’affichage suivant.

Tracer les relations

Nous avons une relation de 1 à plusieurs entre les champs [T_EMPLOYE].[EMP_ID] et [T_ORG].[EMP_SUP] et une seconde entre les champs [T_EMPLOYE].[EMP_ID] et [T_ORG].[EMP_SUB].

Nous avons donc une relation de plusieurs à plusieurs mais avec une seule table mère.

Il faut inclure deux fois la table [T_EMPLOYE] dans le schéma relationnel. La seconde sera renommée par Microsoft Access [T_EMPLOYE_1].

Une autre approche

Relation sur un champ de la même table

Qu'il soit chef ou subordonné, ce sont tous des employés. Alors pourquoi ne pas utiliser une seule table.

L’index du supérieur de l’employé est directement dans un champ de la table des employés, ici le champ [EMP_SUP]. Ce champ contient l’index de la colonne [EMP_ID] du supérieur.

Là aussi nous voyons que « Pierre » n’a pas de supérieur et qu’il a deux subordonnés (« Marie » et « Loïc »).

Le champ [EMP_SUP] doit accepter les valeurs nulles pour les personnes (comme ici « Pierre ») qui n'auraient pas de supérieurs.

Faciliter l’exploitation

De façon identique avec deux tables, il est plus simple de créer une liste de choix sur le champ EMP_SUP.

Nous constatons que « Anne » est subordonnée à « Marie ».

Schéma relationnel

Pour créer le schéma relationnel, comme précédemment, il faut inclure deux fois la table [T_EMPLOYE_ORGA].

La relation se fera entre les champs [T_EMPLOYE_ORGA].[EMP_ID] et [T_EMPLOYE_ORGA_1].[EMP_SUP].
Chose amusante, lorsque l'on ouvre à nouveau la fenêtre des relations, Microsoft Access a changé le sens pour [T_EMPLOYE_ORGA_1].[EMP_ID] vers [T_EMPLOYE_ORGA].[EMP_SUP].

Quelques requêtes

Trouver le supérieur d’un employé

Nous trouvons bien pour l’employé « Marie », le supérieur « Pierre ».

Filtrer les subordonnés à un employé

Nous retrouvons bien les deux subordonnés de « Marie ».

Récupérer ceux sans supérieur

Sans surprise, nous récupérons bien « Pierre ».

Plusieurs colonnes liées

Il est possible de lier plusieurs fois la table mère.

Dans ce cas, il faudra inclure dans les relations deux fois la table [T_GENEALOGIE], une pour le lien [T_ GENEALOGIE].[GEN_MERE] et une pour [GENEALOGIE].[GEN_PERE].

Notes

Microsoft Access utilise cette technique dans les tables systèmes.

Limites

Je déconseille d’utiliser une relation réflexive pour gérer une hiérarchie aussi complexe qu’un organigramme.

Niveaux d’imbrication

Que ce soit sous forme de relation avec deux tables, ou réflexive, cette technique n'est réellement utilisable que s'il n'y a qu'un niveau d'imbrication.

Il devient difficile de manipuler des sous-niveaux.

S'il est aisé de retrouver le supérieur direct de "Anne" qui est "Marie", il devient difficile d'afficher la hiérarchie complète, « Jean » => « Anne » => « Marie » => « Pierre ».

Pas de plusieurs à plusieurs

Un subordonné peut avoir un responsable direct et un seul, dans la réalité, ce ne sera pas forcément le cas.
Loïc pourrait avoir « Pierre » comme responsable, mais aussi « Jean ». Cette structure ne le permet pas. La relation entre [EMP_ID] et [EMP_SUP] est forcément de type 1 à plusieurs.

Un gestionnaire de liste

Nous utilisons souvent ce que j'appelle des listes de confort :

• civilités (madame; monsieur, etc.) ;
• types de téléphone (portable, fixe, etc.) ;
• mois (janvier, février, mars, etc.).

En règle générale, le concepteur créera une table par liste : T_TYPE_TEL – T_CIVILITE – T_MOIS – etc.

Regrouper les tables

Nous avons une table [T_LISTE].

Volontairement, je n'utiliserais pas de clé primaire numérique, mais je la place sur [LIST_ITEM].

Comme précédemment, la table T_LISTE est dupliquée dans la fenêtre des relations pour créer le lien entre [LIST_ITEM] et [LIST_LIEN].

Le champ [LIST_ITEM] contient l’ensemble des termes, et [LIST_LIEN] les valeurs critères de filtres.

Filtrer les catégories

Les enregistrements dont le champ [LISTE_LIEN] est nul, sont considérés comme les titres des listes et seront utilisés comme critères pour filtrer sur une catégorie.

Pour filtrer sur une autre catégorie, il suffit de changer le critère.

Afficher les catégories

La requête est inchangée, seul le critère change.

Ce système est facile à utiliser puisqu'il suffit de copier/coller la requête et changer la valeur du critère.

Formulaire de gestion

Un formulaire [Liste_fr], permet d’ajouter/supprimer/modifier une catégorie et ses items.

Table exemple

La table [T_SAMPLE], affiche les listes filtrées par catégories.

Utilisation

Télécharger le fichier (Microsoft Access 2010).
Pour utiliser ce gestionnaire, exporter la table T_LISTE et le formulaire [Liste_fr] dans votre projet.

---

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.

---