> Data > Gérer des hiérarchies

Gérer des hiérarchies

Data - Par iTPro.fr - Publié le 24 juin 2010
email

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 gratuitement cette ressource

Endpoint Security : Guide de Mise en œuvre

Endpoint Security : Guide de Mise en œuvre

Détournement d’applications légitimes, élévation de privilèges, logiciels malveillants furtifs : comment les solutions de Endpoint Security permettent elles de faire face aux nouvelles techniques d'attaques complexes ? Découvrez, dans ce Guide Endpoint Security, les perspectives associées à leur mise en œuvre.

Data - Par iTPro.fr - Publié le 24 juin 2010