Comment concevoir une base de données pour les champs définis par l'utilisateur ?

Question

Mes exigences sont les suivantes :

• Nécessité d'être en mesure d'ajouter dynamiquement des champs définis par l'utilisateur, quel que soit le type de données.
• Nécessité de pouvoir interroger les UDF rapidement
• Il faut pouvoir effectuer des calculs sur les UDF en fonction du type de données.
• Il faut pouvoir trier les UDF en fonction du type de données.

Autres informations :

• Je recherche avant tout la performance
• Il y a quelques millions de fiches qui peuvent contenir des données UDF.
• La dernière fois que j'ai vérifié, il y avait plus de 50 millions d'enregistrements UDF dans notre base de données actuelle.
• La plupart du temps, un UDF n'est attaché qu'à quelques milliers de fiches, et non à toutes.
• Les UDF ne sont pas jointes ou utilisées comme clés. Ce sont juste des données utilisées pour des requêtes ou des rapports.

Options :

1. Créer une grande table avec StringValue1, StringValue2... IntValue1, IntValue2,... etc. Je déteste cette idée, mais je l'envisagerai si quelqu'un peut me dire qu'elle est meilleure que les autres idées et pourquoi.

2. Créez un tableau dynamique qui ajoute une nouvelle colonne à la demande, selon les besoins. Je n'aime pas non plus cette idée car je pense que les performances seraient lentes à moins d'indexer chaque colonne.

3. Créez un tableau unique contenant UDFName, UDFDataType et Value. Lorsqu'un nouvel UDF est ajouté, générez une vue qui récupère uniquement ces données et les analyse en fonction du type spécifié. Les éléments qui ne répondent pas aux critères d'analyse syntaxique renvoient NULL.

4. Créez plusieurs tables UDF, une par type de données. Nous aurions ainsi des tables pour les UDFStrings, les UDFDates, etc. Il est probable que nous fassions la même chose que le n°2 et que nous générions automatiquement une vue chaque fois qu'un nouveau champ est ajouté.

5. Types de données XML ? Je n'ai pas encore travaillé avec ces types de données, mais j'en ai entendu parler. Je ne suis pas sûr qu'ils me donneraient les résultats que je souhaite, notamment en termes de performances.

6. Quelque chose d'autre ?

Answer 1

Si les performances sont la préoccupation principale, je choisirais le point 6... une table par UDF (en fait, c'est une variante du point 2). Cette réponse est spécifiquement adaptée à cette situation et à la description de la distribution des données et des modèles d'accès décrits.

Pour :

1. Parce que vous indiquez que certains UDF ont des valeurs pour une petite portion de l'ensemble des données, un tableau séparé table séparée vous donnerait la meilleure performance parce que cette table sera sera seulement aussi grande qu'elle doit l'être pour prendre en charge l'UDF. Il en va de même pour les indices connexes.

2. Vous bénéficiez également d'un gain de vitesse en limitant la quantité de données à traiter pour les agrégations ou autres transformations. La division des données en plusieurs tables vous permet d'effectuer une partie de l'agrégation et d'autres analyses statistiques sur les données UDF, puis de joindre ce résultat à la table principale via une clé étrangère pour obtenir les attributs non agrégés.

3. Vous pouvez utiliser des noms de table/colonne qui reflètent ce que sont réellement les données.

4. Vous avez le contrôle total de l'utilisation des types de données, des contraintes de contrôle, des valeurs par défaut, etc. pour définir les domaines de données. Ne sous-estimez pas l'impact sur les performances résultant de la conversion des types de données à la volée. Ces contraintes contraintes aident également les optimiseurs de requêtes des SGBDR à de requêtes du SGBDR à développer des plans plus efficaces.

5. Si jamais vous avez besoin d'utiliser des étrangères, des clés déclaratives intégrées référentielle déclarative intégrée est rarement surpassée par l'application de contraintes au niveau des applications ou l'application de contraintes.

Cons :

1. Cela pourrait créer beaucoup de tables. L'application de la séparation des schémas et/ou d'une convention de nommage permettrait d'atténuer ce problème.

2. Il y a plus de code d'application nécessaire pour gérer la définition et la gestion et la gestion. Je pense que c'est encore moins de code nécessaire que pour les options originales 1, 3 et 4.

Autres considérations :

1. S'il y a quelque chose à propos du nature des données qui ferait que un sens pour les UDF à être regroupés, cela devrait être encouragé. De cette façon, ces éléments de données peuvent être combinés dans un seul tableau. Par exemple, disons que vous avez des UDFs pour la couleur, la taille et le coût. La tendance dans les données est que la plupart des instances de ces données ressemblent à

    'red', 'large', 45.03 

   plutôt que

    NULL, 'medium', NULL

   Dans ce cas, vous ne subirez pas de pénalité de vitesse notable en combinant les 3 colonnes dans un seul tableau car peu de valeurs seraient NULL et vous évitez de créer 2 tables supplémentaires, ce qui représente 2 jointures de moins nécessaires lorsque lorsque vous avez besoin d'accéder aux trois colonnes.

2. Si vous vous heurtez à un mur de performance à partir d'un UDF qui est très peuplé et fréquemment fréquemment utilisé, il faut alors envisager de l'inclure dans la table principale.

3. La conception de tableaux logiques peut vous amener à un certain point, mais lorsque le nombre d'enregistrements d'enregistrements devient vraiment massif, vous devez aussi vous devez également commencer à examiner les options de partitionnement des tables proposées par le SGBDR de votre choix.

Answer 2

J'ai écrit sur à propos de ce problème beaucoup . La solution la plus courante est l'anti-modèle Entité-Attribut-Valeur, qui est similaire à ce que vous décrivez dans votre option n° 3. Évitez ce design comme la peste .

Ce que j'utilise pour cette solution lorsque j'ai besoin de champs personnalisés vraiment dynamiques, c'est de les stocker dans un blob de XML, de sorte que je puisse ajouter de nouveaux champs à tout moment. Mais pour accélérer le processus, créez également des tables supplémentaires pour chaque champ sur lequel vous avez besoin d'effectuer des recherches ou des tris (vous n'avez pas besoin d'une table par champ, juste d'une table par consultable sur champ). C'est ce qu'on appelle parfois une conception à index inversé.

Vous pouvez lire un article intéressant de 2009 sur cette solution ici : http://backchannel.org/blog/friendfeed-schemaless-mysql

Vous pouvez aussi utiliser une base de données orientée vers les documents, où l'on s'attend à ce que vous ayez des champs personnalisés par document. Je choisirais Solr .

Answer 3

Ce problème semble être mieux résolu par une solution non relationnelle, comme MongoDB ou CouchDB.

Ils permettent tous deux une expansion dynamique du schéma tout en vous permettant de maintenir l'intégrité des tuple que vous recherchez.

Je suis d'accord avec Bill Karwin, le modèle EAV n'est pas une approche performante pour vous. L'utilisation de paires nom-valeur dans un système relationnel n'est pas intrinsèquement mauvaise, mais ne fonctionne bien que lorsque la paire nom-valeur constitue un tuple complet d'informations. Lorsque son utilisation vous oblige à reconstruire dynamiquement une table au moment de l'exécution, toutes sortes de choses commencent à devenir difficiles. L'interrogation devient un exercice de maintenance des pivots ou vous oblige à pousser la reconstruction des n-uplets dans la couche objet.

Vous ne pouvez pas déterminer si une valeur nulle ou manquante est une entrée valide ou une absence d'entrée sans intégrer des règles de schéma dans votre couche objet.

Vous perdez la capacité de gérer efficacement votre schéma. Un varchar de 100 caractères est-il le bon type pour le champ "valeur" ? 200 caractères ? Devrait-il être remplacé par nvarchar ? Il peut s'agir d'un compromis difficile, qui vous oblige à imposer des limites artificielles à la nature dynamique de votre ensemble. Quelque chose comme "vous ne pouvez avoir que x champs définis par l'utilisateur et chacun ne peut avoir que y caractères".

Avec une solution orientée document, comme MongoDB ou CouchDB, vous conservez tous les attributs associés à un utilisateur dans un seul tuple. Puisque les jointures ne sont pas un problème, la vie est heureuse, car aucune de ces deux solutions ne fonctionne bien avec les jointures, malgré le battage médiatique. Vos utilisateurs peuvent définir autant d'attributs qu'ils le souhaitent (ou que vous le permettez) à des longueurs qui ne deviennent pas difficiles à gérer avant d'atteindre environ 4 Mo.

Si vos données nécessitent une intégrité de niveau ACID, vous pouvez envisager de diviser la solution, en plaçant les données à haute intégrité dans votre base de données relationnelle et les données dynamiques dans un magasin non relationnel.

Answer 4

Je créerais très probablement un tableau de la structure suivante :

• varchar Nom
• varchar Type
• Valeur numérique décimale
• varchar StringValue
• date DateValue

Les types exacts de cours dépendent de vos besoins (et bien sûr des dbms que vous utilisez). Vous pouvez également utiliser le champ NumberValue (décimal) pour les int et les booléens. Vous pouvez également avoir besoin d'autres types.

Vous avez besoin d'un lien vers les fiches qui possèdent la valeur. Le plus simple et le plus rapide est probablement de créer une table de champs utilisateur pour chaque table maître et d'ajouter une simple clé étrangère. De cette façon, vous pouvez filtrer les fiches par champs utilisateur facilement et rapidement.

Il est possible que vous souhaitiez disposer d'une sorte d'information sur les métadonnées. Vous obtenez donc ce qui suit :

Tableau UdfMetaData

• int id
• varchar Nom
• varchar Type

Tableau MasterUdfValues

• int Master_FK
• int MetaData_FK
• Valeur numérique décimale
• varchar StringValue
• date DateValue

---

Quoi que vous fassiez, je voudrais no modifier dynamiquement la structure de la table. C'est un cauchemar de maintenance. Je voudrais également no utilisent des structures XML, elles sont beaucoup trop lentes.

Answer 5

Même si vous permettez à un utilisateur d'ajouter des colonnes personnalisées, les requêtes sur ces colonnes ne seront pas nécessairement performantes. La conception des requêtes comporte de nombreux aspects qui leur permettent d'être performantes, dont le plus important est la spécification correcte de ce qui doit être stocké en premier lieu. Ainsi, fondamentalement, voulez-vous permettre aux utilisateurs de créer des schémas sans réfléchir aux spécifications et être en mesure de dériver rapidement des informations à partir de ces schémas ? Si c'est le cas, il est peu probable qu'une telle solution s'adapte bien, surtout si vous voulez permettre à l'utilisateur d'effectuer des analyses numériques sur les données.

Option 1

Cette approche vous donne un schéma sans savoir ce qu'il signifie, ce qui est une recette pour un désastre et un cauchemar pour les concepteurs de rapports. En d'autres termes, vous devez disposer des métadonnées pour savoir quelle colonne stocke quelles données. Si ces métadonnées sont mélangées, vos données risquent d'être endommagées. De plus, il est facile de mettre les mauvaises données dans la mauvaise colonne. ("Quoi ? String1 contient le nom des couvents ? Je pensais que c'était les drogues préférées de Chalie Sheen.")

Options 3, 4 et 5

OMI, les exigences 2, 3 et 4 éliminent toute variation d'une VAE. Si vous devez interroger, trier ou effectuer des calculs sur ces données, l'EAV est le rêve de Cthulhu et le cauchemar de votre équipe de développement et de votre DBA. Les EAV créeront un goulot d'étranglement en termes de performances et ne vous donneront pas l'intégrité des données dont vous avez besoin pour obtenir rapidement les informations que vous voulez. Les requêtes se transformeront rapidement en nœuds gordiens de type crosstab.

Option 2,6

Il ne reste donc qu'un seul choix : rassembler les spécifications et élaborer ensuite le schéma.

Si le client souhaite obtenir les meilleures performances sur les données qu'il souhaite stocker, il doit alors passer par le processus de collaboration avec un développeur pour comprendre ses besoins afin que ces données soient stockées aussi efficacement que possible. Les données peuvent toujours être stockées dans une table séparée du reste des tables avec un code qui construit dynamiquement un formulaire basé sur le schéma de la table. Si vous disposez d'une base de données qui permet d'étendre les propriétés des colonnes, vous pouvez même les utiliser pour aider le créateur de formulaire à utiliser de belles étiquettes, des infobulles, etc. de sorte que tout ce qui est nécessaire est d'ajouter le schéma. Quoi qu'il en soit, pour créer et exécuter des rapports efficacement, les données doivent être stockées correctement. Si les données en question comportent de nombreux zéros, certaines bases de données ont la capacité de stocker ce type d'information. Par exemple, SQL Server 2008 dispose d'une fonctionnalité appelée Sparse Columns, spécifiquement destinée aux données comportant de nombreux zéros.

S'il ne s'agissait que d'un sac de données sur lequel aucune analyse, aucun filtrage ni aucun tri ne devait être effectué, je dirais qu'une variation de l'EAV pourrait faire l'affaire. Cependant, étant donné vos exigences, la solution la plus efficace sera d'obtenir les spécifications appropriées, même si vous stockez ces nouvelles colonnes dans des tables séparées et construisez des formulaires de manière dynamique à partir de ces tables.

Colonnes éparses