par Itzik Ben-Gan
Avec des déclencheurs et T-SQL, on peut mettre sur pied une solution de gestion
des données hiérarchiques fonctionnelle, et qui assure sa propre maintenance.
Une start-up Internet attire Andrew vers un nouveau poste ; Steven et Michael,
qui étaient sous sa responsabilité, ont besoin d'un nouveau manager. Super Janet
prend Steven et Michael sous son aile bien qu'elle supervise déjà directement
Robert, Laura et Ann Robert, à son tour, va diriger sa propre équipe. Mais récemment,
Robert a commencé à chercher un poste proposant des horaires plus attrayants et
un meilleur salaire. Si Robert quitte la société, qui va chapeauter David, Ron
et Dan, sans compter James, l'assistant de David ? Et, plus important encore,
comment le service informatique va-t-il répercuter tous ces changements de managers
dans l'organigramme de la société ?
Les structures hiérarchiques, également appelées arbres, présentent des dépendances
hiérarchiques entre leurs membres. Une structure hiérarchique classique est constituée
d'un organigramme décrivant les relations entre les employés d'une entreprise.
Un manager est chargé de superviser certains employés, ces employés peuvent à
leur tour être chargés de gérer d'autres employés, etc…
Ni, le langage SQL, ni SQL Server ne dispose d'un support intégré pour
ces structures hiérarchiques
Ni, le langage SQL, ni SQL Server ne disposent d'un support intégré pour ces structures
hiérarchiques. Alors, comment traiter les hiérarchies avec des systèmes de gestion
de base de données relationnelle (SGBDR), tels que SQL Server ? Considérez la
figure 1, qui montre un organigramme simple. On remarque que chaque employé a
un supérieur hiérarchique, sauf Nancy qui est la responsable au niveau le plus
haut. La façon la plus courante de représenter une telle structure dans une base
de données relationnelle est d'utiliser des paires de colonnes : une colonne comportant
les ID des employés (les enfants) et l'autre, les ID de leurs managers (les parents).
Le problème avec cette solution est que Nancy n'a pas de supérieur hiérarchique,
mais qu'il faut tout de même mettre une valeur dans sa colonne Manager ID. Pour
résoudre ce problème, il suffit de mettre un NULL dans la colonne Manager ID.
Une autre solution serait d'enregistrer l'ID de Nancy dans la colonne Manager
ID et faire ainsi de Nancy son propre chef.
Pour bien voir comment maintenir les hiérarchies avec SQL Server, créez une table
simple contenant les informations concernant les employés de l'organigramme de
la figure 1. On peut alors utiliser des déclencheurs, des requêtes T-SQL et des
procédures cataloguées pour suivre une ID d'employé, un nom d'employé, une ID
de manager et le salaires de l'employé et de son supérieur hiérarchique lorsqu'un
nouvel embauché rejoint la société, change de poste dans l'entreprise, ou lorsqu'il
quitte la société. Pour les besoins de notre exemple, utilisez NULL comme valeur
de l'ID du manager de Nancy.
Gérer des hiérarchies
Sans ajouter d’informations supplémentaire à la table Employees, on peut utiliser
les instructions T-SQL pour répondre à certaines questions sur la hiérarchie des
employés. Par exemple, pour trouver qui est le responsable le plus senior de l’entreprise,
on peut exécuter la requête suivante :
SELECT *
FROM Employees
WHERE mgrid IS NULL
Pour trouver les noms de l’ensemble des employés et de leur supérieur hiérarchique,
on peut spécifier :
SELECT E.empname AS EmployeeName, M.empname AS ManagerName
FROM Employees AS E LEFT OUTER JOIN Employees AS M
ON E.mgrid = M.empid
Cette requête utilise une jointure externe, car une jointure interne exclurait
Nancy, la responsable au grade le plus élevé, du rapport. La clause LEFT OUTER
JOIN inclut toutes les lignes de la table de gauche (la table Employees), que
ces lignes aient ou non une correspondance dans la table de droite (représentant
les managers).
La requête suivante renvoie la liste des subordonnés immédiats de Robert :
SELECT *
FROM Employees
WHERE mgrid = 7
Et, pour afficher l’ensemble des employés du bas de l’échelle (c’est-à -dire, les
employés sans subalternes), on peut exécuter la requête suivante :
SELECT *
FROM Employees AS M
WHERE NOT EXISTS (SELECT empid
FROM Employees AS E
WHERE E.mgrid = M.empid)
Cette requête est une sous-requête associée qui renvoie uniquement les employés
qui n’ont pas rang de manager.
Téléchargez cette ressource
Livre blanc Sécurité et Stockage des documents
Découvrez dans ce livre blanc Kyocera les outils logiciels qui permettent une approche holistique et efficace de la collecte, du stockage, de la gestion et de la sécurisation des documents en entreprise.
Les articles les plus consultés
- ActiveViam fait travailler les data scientists et les décideurs métiers ensemble
- L’utilisation des données pour survivre !
- La blockchain en pratique
- Stockage autonome, Evolutivité & Gestion intelligente, Pure Storage offre de nouvelles perspectives aux entreprises
- Intelligence Artificielle : DeepKube sécurise en profondeur les données des entreprises
