Schéma de database pour les groupes hiérarchiques

Je travaille sur une design de database pour la hiérarchie des groupes utilisée comme base d'un système plus grand. Chaque groupe peut contenir d'autres groupes, ainsi que des 'périphériques' en tant qu'objects feuille (rien ne se trouve en dessous du périphérique).

La database utilisée est MS SQL 2005. (Bien que travailler avec MS SQL 2000 soit un bonus, une solution nécessitant MS SQL 2008 n'est malheureusement pas réalisable pour le moment).

Il existe différents types de groupes, qui doivent être dynamics et définissables au moment de l'exécution par les users. Par exemple, les types de groupes peuvent être "client", "count", "ville" ou "bâtiment", "étage", et chaque type va avoir un set d'attributes différent, définissable par l'user. Il y aura également des règles de gestion appliquées – par exemple, un "plancher" ne peut être contenu que sous un groupe "bâtiment", et encore une fois, ceux-ci sont définissables à l'exécution.

Une grande partie de la fonctionnalité de l'application provient de l'exécution de rapports basés sur ces groupes, il doit donc y avoir un moyen relativement rapide d'get une list de tous les périphériques contenus dans un certain groupe (et tous les sous-groupes).

Le stockage de groupes utilisant la technique de traversée d'tree pré-command modifiée a l'avantage d'être rapide, mais l'inconvénient est qu'il est assez complexe et fragile – si des users / applications externes modifient la database, il existe un risque de rupture totale. Nous implémentons également une couche ORM, et cette méthode semble compliquer l'utilisation de relations dans la plupart des bibliothèques ORM.

L'utilisation d' expressions de table communes et d'une relation "standard" id / groupes parentid semble être un moyen puissant d'éviter l'exécution de plusieurs requêtes récursives. Y a-t-il un inconvénient à cette méthode?

En ce qui concerne les attributes, quelle est la meilleure façon de les stocker? Une table longue et étroite qui renvoie au groupe? Est-ce qu'un atsortingbut commun, comme "nom", doit être stocké dans une table de groupes, au lieu de la table des attributes (la plupart du time, le nom sera tout ce qu'il faut afficher)?

Y aura-t-il des problèmes de performances en utilisant cette méthode (supposons une moyenne élevée de 2000 groupes avec une moyenne de 6 attributes chacun, et une moyenne de 10 users simultanés, sur un matériel raisonnable, par exemple, 4 cœurs Xeon 2 Ghz , en excluant tout autre process)?

N'hésitez pas à suggérer un schéma complètement différent de ce que j'ai décrit ici. J'essayais juste d'illustrer les problèmes qui m'inquiètent.

Je vous recommand de build la manière la plus facile à maintenir (la configuration parent / enfant "standard") et d'y exécuter au less quelques tests de base.

Vous seriez surpris de savoir ce qu'un moteur de database peut faire avec l'indexing appropriée, en particulier si votre set de données peut entrer dans la memory.

En supposant 6 attributes par groupe, 2000 groupes et 30 octets / atsortingbut, vous parlez 360KB * éléments / groupe attendus – figure 400KB. Si vous prévoyez d'avoir 1000 éléments / groupe, vous ne regardez que 400 Mo de données – cela va tenir dans la memory sans problème, et les bases de données sont rapides aux jointures lorsque toutes datatables sont en memory.

Les expressions de table communes vous permettront de sortir une list de groupes avec les relations parent-enfant. Voici un exemple de sproc utilisant CTE pour une application différente. C'est raisonnablement efficace mais méfiez-vous des mises en garde suivantes:

Si une partie se produit plus d'une fois dans la hiérarchie, elle sera signalée à chaque location. Vous devrez peut-être post-traiter les résultats.
Les CTE sont quelque peu obtus et offrent une scope limitée pour filterr les résultats dans la requête – le CTE peut ne pas apparaître plus d'une fois dans l'instruction select.

Le CONNECT BY d'Oracle est un peu plus flexible car il n'impose pas autant de limitations à la structure de la requête que le CTE, mais si vous utilisez SQL Server, ce n'est pas une option.

Si vous avez besoin de faire quelque chose d'intelligent avec les résultats intermédiaires, alors écrivez un sproc qui utilise le CTE pour get une requête brute dans une table temporaire et travailler à partir de là. SELECT INTO minimisera le trafic généré dans ce cas. La table résultante sera dans le cache afin que les opérations sur elle sera raisonnablement rapide.

Quelques optimizations physiques possibles qui pourraient aider:

Index clusterisés sur le parent afin que la sortie des nœuds enfants pour un parent utilise less d'E / S.
Beaucoup de RAM et (en fonction de la taille de votre table de nomenclature) des servers 64 bits avec encore plus de RAM pour que la table de nomenclature principale puisse être caching dans le kernel. Sur un O / S 32 bits, le commutateur de démarrage / 3G est votre ami et n'a pas de réel inconvénient pour un server de database
DBCC PINTABLE peut aider à forcer le gestionnaire de database à maintenir la table dans le cache.

Type d'atsortingbut parent – Les tables de encoding d'atsortingbut ne fonctionneront pas bien avec les CTE car vous obtiendrez une explosion combinatoire dans vos counts de lignes si vous incluez la table atsortingbutaire. Cela exclurait toute logique métier dans la requête filtrée sur les attributes. Il serait préférable de stocker les attributes directement sur l'input de la table de nomenclature.

Arbre de pré-command Traversée est très pratique. Vous pouvez le rendre robuste en gardant les numéros de traversée à jour avec les triggersurs.

Une technique similaire que j'ai utilisée est de garder une table séparée de (ancestor_id, descendant_id) qui list tous les ancêtres et les descendants. C'est presque aussi bon que les numéros de traversée pré-command.

L'utilisation d'une table séparée est pratique, car même si elle introduit une jointure supplémentaire, elle supprime la complexité dans une table distincte.

La pré-command modifiée est essentiellement la méthode Nested Sets de Joe Celko. Son livre, "Les trees et les hiérarchies …" couvre à la fois la list d'adjacence et NS, avec des descriptions des avantages et des inconvénients de chacun. Avec une indexing correcte, CTE des lists d'adjacence obtient les performances les plus équilibrées. Si vous allez lire pour la plupart, alors NS sera plus rapide.

Ce que vous semblez décrire est un processeur de nomenclature. Bien que non M $, Graeme Birchall a un livre DB2 gratuit, avec un chapitre sur le traitement hiérarchique en CTE (la syntaxe est pratiquement identique, IIRC, en ce que la syntaxe ANSI a adopté DB2, que M $ a ensuite adopté): http: // mysite .verizon.net / Graeme_Birchall / livre de recettes / DB2V95CK.PDF