Recherche d'un contrôle d'intégrité de la conception des tables SQL Server pour les tables de profil avec des colonnes dynamiques

Question

EST-IL JUDICIEUX DE GÉNÉRALISER UNE TABLE DE PROFIL POUR DEUX PROFILS DISTINCTS DANS LE SYSTÈME, DANS MON CAS, LES ENSEIGNANTS ET LES ÉTUDIANTS ? C'est ce que je suis en train de faire et je voudrais juste une vérification générale de la cohérence de mon approche de la conception. Les réponses sont les bienvenues. Contexte ci-dessous :

Nous construisons un système web qui comporte à la fois des enseignants et des étudiants. Les deux ont des comptes dans le système. Les deux ont des profils dans le système.

Ma question porte sur la conception de ces tables profilées.

Le profil de l'enseignant est assez statique en ce qui concerne les métadonnées qui lui sont associées. Chaque enseignant dispose d'un certain nombre de champs qui exposent des informations sur cet individu (écoles, diplômes, etc.). Il en va différemment pour les étudiants. Nous utilisons un service Windows pour extraire des données variables sur les étudiants à partir d'un flux infini de feuilles de calcul Excel.

Les données sont transférées dans notre base de données et les champs apparaissent ensuite en association avec le profil de l'étudiant. Par conséquent, chaque étudiant peut avoir des champs très différents dans son profil.

J'ai commencé avec le concept de trois tables :

Comptes

AccountID

Profils des enseignants

TeacherProfileID
AccountID
SecondarySchool
University
YearsTeaching
Etc...

Profil de l'étudiant

StudentProfileID
AccountID
Header
Value

En StudentProfiles contiendrait le nom des en-têtes de colonne des feuilles de calcul Excel et les valeurs associées.

Depuis, j'ai légèrement modifié la conception afin de traiter les profils de manière plus générique, conformément à l'image ERD ci-jointe. Les "en-têtes" de l'enseignant et de l'étudiant sont stockés dans une table appelée ProfileAttributeTypes et les réponses (provenant du document Excel ou des champs de saisie du formulaire web) sont placées dans un fichier ProfileAttributes table. De cette manière, les profils des étudiants et des enseignants peuvent être associés à un flux dynamique de champs de profil. Le tableau "Permissions" indique s'il s'agit d'un étudiant ou d'un enseignant.

Comme ce système est appelé à se développer rapidement, je veux m'assurer que les fondations sont solides. Pourriez-vous me faire part de vos commentaires sur cette conception et me dire si elle vous semble judicieuse ou si vous voyez des problèmes qu'elle pourrait créer et, le cas échéant, quelle pourrait être une meilleure approche ?

Merci d'avance.

Answer 1

Approche par sac de propriété

Le modèle de données que vous proposez repose sur un "sac de propriétés" (une collection d'éléments clé-valeur pour un profil). L'élégance de ce modèle réside dans le fait que vous pouvez étendre vos propriétés sans avoir à modifier le modèle de données.

L'inconvénient est que vous devrez très souvent "pivoter" les données et que la taille de vos tables (et index) augmentera très rapidement. (mon expérience : 200 propriétés par clé pour 50K enregistrements = 10 millions de propriétés sans rien pour suivre les changements sur les propriétés).

Ce modèle peut être recommandé si vous devez principalement interroger une propriété spécifique pour une clé. Pensez à des requêtes telles que "Combien de personnes ont un diplôme de mathématiques ?" où le diplôme de mathématiques est une clé de propriété.

Approche des champs Xml

Avec cette stratégie, nous ajoutons un champ "xml" à la rubrique Profiles qui prend la liste des propriétés sous la forme d'un xml. Ce modèle permet également d'augmenter le nombre de propriétés sans devoir modifier le modèle de données.

Sql Server prend très bien en charge ces champs (par le biais de requêtes xpath, d'index xml, etc.) et l'avantage est bien sûr que vous conservez un modèle de données simple qui vous permet de stocker ce que vous voulez dans le champ xml.

Ce modèle est recommandé lorsque le contenu du champ est remplacé dans son ensemble. Il est possible de modifier les données d'un champ xml à l'aide de requêtes xpath, mais cette méthode est assez lente.

Colonnes éparses

En Colonne éparse a été introduit dans SQL Server 2008 pour vous permettre de créer un grand nombre de champs différents dans une table qui n'est pas densément peuplée. L'avantage est qu'il vous permet de créer plus de colonnes que la limite de 1024 et que les champs non peuplés n'occupent pas d'espace lorsqu'ils ne sont pas remplis.

L'inconvénient est que vous devez connaître tous les champs possibles dès le départ, faute de quoi le modèle de données sera modifié à chaque fois qu'un nouveau champ sera rencontré. Ce modèle est idéal si votre table comporte essentiellement des colonnes vides.

Quelle approche adopter ?

C'est la partie la plus difficile, tout dépend de ce que vous voulez faire avec les données. D'après mon expérience, l'approche par sac de propriétés fonctionne bien avec de petits ensembles de données et si vous n'avez pas besoin de suivre les modifications apportées aux propriétés. (J'ai vu des situations avec plus d'un milliard d'enregistrements dans la table après 1 mois).

Le champ Xml peut être une véritable plaie lorsque vous devez souvent faire des requêtes sur le contenu spécifique du champ, mais il fonctionne très bien pour stocker des informations qui ne seront demandées que "par clé"

Les colonnes éparses fonctionnent bien lorsque les colonnes sont remplies pour moins de 30 à 40 % des enregistrements.

Note complémentaire : Il est considéré comme une mauvaise pratique de stocker des éléments tels que "années d'enseignement" dans votre modèle de données, car vous devrez constamment mettre à jour la valeur. Il est préférable de stocker "année de début d'enseignement" et de calculer le delta.

Answer 2

Je ne pense pas que la conception que vous avez soit très bonne. Le modèle mélange les notions d'utilisateur et d'entité.

Voici le début d'une conception plus appropriée.

t_Utilisateur

t_User_Settings (Profil)

t_Permissions

t_Actions

t_Étudiant

t_Teacher

t_Student_Attributes

t_Teacher_attributes

Les éléments/attributs liés à l'utilisateur appartiennent à t_User ou à t_User_Settings Les éléments/attributs liés au domaine appartiennent à t_Teacher/t_Teacher_Attributes ou t_Student/t_Student_Attributes.

Vous pouvez relier le concept de domaine (enseignant/étudiant) au concept d'utilisateur via une clé étrangère. Vous pouvez également créer les tables t_Teacher_User + t_Student_User.

Remarquez qu'il suffit de lire les noms des tables pour savoir exactement ce qui va où.

Answer 3

D'après mon expérience, la meilleure façon de vérifier le bien-fondé d'un modèle de données est d'élaborer les requêtes / DML dont vous aurez probablement besoin.

Comme l'écrit Filip de Vos, votre approche de "sac de propriété" ne se prête pas facilement aux requêtes relationnelles typiques - "select count(*) from students where course = 'maths' and score > 12" serait très pénible.

D'autre part, votre conception initiale résout les problèmes liés au stockage de données dont le schéma varie ou est inconnu au moment de la conception.

En pratique, vous finissez généralement par modéliser les éléments "fixes" dans un modèle relationnel typique et par utiliser un sac de propriétés ou un document XML pour modéliser les éléments variables. Si vous pouvez être clair sur le schéma au moment de la conception, les "colonnes clairsemées" peuvent également vous aider.