Comment concevoir une firebase database pour les champs définis par l’utilisateur?

Mes exigences sont:

  • Besoin de pouvoir append dynamicment des champs définis par l’utilisateur de n’importe quel type de données
  • Besoin de pouvoir interroger rapidement les fichiers UDF
  • Besoin de pouvoir effectuer des calculs sur les fonctions définies par l’utilisateur en fonction du type de données
  • Besoin de pouvoir sortinger les fichiers UDF en fonction du type de données

Les autres informations:

  • Je cherche avant tout la performance
  • Il y a quelques millions de fichiers Master qui peuvent être associés à des données UDF
  • Lors de ma dernière vérification, notre firebase database actuelle contenait plus de 50 millions d’enregistrements UDF.
  • La plupart du temps, un fichier UDF n’est attaché qu’à quelques milliers de fiches, pas toutes
  • Les fichiers UDF ne sont pas joints ou utilisés comme clés. Ce ne sont que des données utilisées pour des requêtes ou des rapports

Options:

  1. Créez une grande table avec SsortingngValue1, SsortingngValue2 … IntValue1, IntValue2, etc. Je déteste cette idée, mais je la considérerai si quelqu’un peut me dire que c’est mieux que d’autres idées et pourquoi.

  2. Créez une table dynamic qui ajoute une nouvelle colonne à la demande si nécessaire. Je n’aime pas non plus cette idée car je pense que la performance serait lente à moins d’indexer chaque colonne.

  3. Créez une seule table contenant UDFName, UDFDataType et Value. Lorsqu’un nouveau fichier UDF est ajouté, générez une vue qui extrait uniquement ces données et les parsing selon le type spécifié. Les éléments qui ne répondent pas aux critères d’parsing renvoient NULL.

  4. Créez plusieurs tables UDF, une par type de données. Nous aurions donc des tables pour UDFSsortingngs, UDFDates, etc. Probablement faire la même chose que # 2 et générer automatiquement une vue quand un nouveau champ est ajouté

  5. XML DataTypes? Je n’ai pas travaillé avec ces derniers, mais je les ai vus. Je ne sais pas s’ils me donneraient les résultats que je veux, en particulier avec la performance.

  6. Autre chose?

Si la performance est la principale préoccupation, j’irais avec # 6 … une table par UDF (en fait, c’est une variante de # 2). Cette réponse est spécifiquement adaptée à cette situation et à la description des schémas de dissortingbution et d’access aux données décrits.

Avantages:

  1. Comme vous indiquez que certaines UDF ont des valeurs pour une petite partie de l’dataset, une table distincte vous donnera les meilleures performances, car cette table sera aussi grande que nécessaire pour prendre en charge la fonction UDF. La même chose vaut pour les indices associés.

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

  3. Vous pouvez utiliser des noms de table / colonne qui reflètent les données actuelles.

  4. Vous disposez d’un contrôle complet pour utiliser les types de données, vérifier les contraintes, les valeurs par défaut, etc. pour définir les domaines de données. Ne sous-estimez pas l’impact de la conversion de type de données à la volée. De telles contraintes aident également les optimiseurs de requêtes SGBDR à développer des plans plus efficaces.

  5. Si vous avez besoin d’utiliser des clés étrangères, l’intégrité référentielle déclarative intégrée est rarement dépassée par l’application de contraintes basées sur des déclencheurs ou des applications.

Les inconvénients:

  1. Cela pourrait créer beaucoup de tables. Appliquer la séparation des schémas et / ou une convention d’atsortingbution de noms atténuerait cela.

  2. Il y a plus de code d’application nécessaire pour faire fonctionner la définition et la gestion UDF. Je pense que c’est encore moins de code que pour les options 1, 3 et 4 d’origine.

Autres considérations:

  1. Si la nature des données présente un intérêt pour le regroupement des FDU, cela devrait être encouragé. De cette manière, ces éléments de données peuvent être combinés en une seule table. Par exemple, supposons que vous ayez des UDF 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 rencontrerez pas de pénalité de vitesse notable en combinant les 3 colonnes de la table 1 car peu de valeurs seraient NULL et vous éviterez de créer 2 tables supplémentaires, soit 2 jointures de moins lorsque vous devez accéder aux 3 colonnes. .

  2. Si vous atteignez un mur de performances à partir d’un fichier UDF fortement peuplé et fréquemment utilisé, vous devez l’inclure dans la table principale.

  3. La conception de tables logiques peut vous mener jusqu’à un certain point, mais lorsque le nombre d’enregistrements devient vraiment énorme, vous devriez également commencer à examiner les options de partitionnement de tables proposées par votre SGBDR.

J’ai beaucoup écrit sur ce problème. La solution la plus courante est l’anti-modèle Entity-Atsortingbute-Value, qui est similaire à ce que vous décrivez dans votre option # 3. Évitez cette conception comme la peste .

Ce que j’utilise pour cette solution lorsque j’ai besoin de champs personnalisés vraiment dynamics, c’est de les stocker dans un object XML, afin de pouvoir append de nouveaux champs à tout moment. Mais pour le rendre rapide, créez également des tables supplémentaires pour chaque champ que vous devez rechercher ou sortinger (vous n’avez pas de tableau par champ – juste une table par champ de recherche ). Ceci est parfois appelé un design d’index inversé.

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

Vous pouvez également utiliser une firebase database orientée document, dans laquelle vous êtes censé avoir des champs personnalisés par document. Je choisirais Solr .

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

  • Nom de varchar
  • Type de varchar
  • Nombre décimal Valeur
  • varchar SsortingngValue
  • 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 int et booléens. Vous pourriez aussi avoir besoin d’autres types.

Vous avez besoin d’un lien vers les enregistrements maîtres qui possèdent la valeur. Il est probablement plus facile et plus rapide de créer une table de champs utilisateur pour chaque table principale et d’append une simple clé étrangère. De cette façon, vous pouvez filtrer facilement et rapidement les fichiers maîtres par les champs utilisateur.

Vous voudrez peut-être avoir des informations sur les métadonnées. Donc, vous vous retrouvez avec les éléments suivants:

Table UdfMetaData

  • int id
  • Nom de varchar
  • Type de varchar

Table MasterUdfValues

  • int Master_FK
  • int MetaData_FK
  • Nombre décimal Valeur
  • varchar SsortingngValue
  • date DateValue

Quoi que vous fassiez, je ne changerais pas la structure de la table de manière dynamic. C’est un cauchemar de maintenance. Je n’utiliserais pas non plus les structures XML, elles sont beaucoup trop lentes.

Cela ressemble à un problème qui pourrait être mieux résolu par une solution non relationnelle, comme MongoDB ou CouchDB.

Ils permettent tous deux l’extension dynamic du schéma tout en vous permettant de conserver l’intégrité du 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 insortingnsèquement mauvaise, mais ne fonctionne que lorsque la paire nom-valeur fournit un tuple complet d’informations. Lorsque vous l’utilisez, vous êtes obligé de reconstruire dynamicment une table au moment de l’exécution, toutes sortes de choses deviennent difficiles. L’interrogation devient un exercice de maintenance du pivot ou vous oblige à pousser la reconstruction du tuple dans le calque d’object.

Vous ne pouvez pas déterminer si une valeur nulle ou manquante est une entrée valide ou une absence d’entrée sans incorporer de règles de schéma dans votre couche d’object.

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 “value”? 200 caractères? Devrait-il être nvarchar à la place? Il peut s’agir d’un compromis difficile et vous obliger à imposer des limites artificielles à la nature dynamic de votre set. Quelque chose comme “vous ne pouvez avoir que deux champs définis par l’utilisateur et chacun ne peut contenir que des caractères de long.

Avec une solution orientée document, comme MongoDB ou CouchDB, vous conservez tous les atsortingbuts associés à un utilisateur dans un seul tuple. Puisque les jointures ne sont pas un problème, la vie est heureuse, car aucun de ces deux ne réussit bien avec les jointures, malgré le battage médiatique. Vos utilisateurs peuvent définir autant d’atsortingbuts qu’ils le souhaitent (ou vous autoriserez) à des longueurs qui ne sont pas difficiles à gérer jusqu’à ce que vous atteigniez environ 4 Mo.

Si vous avez des données nécessitant une intégrité au niveau ACID, vous pouvez envisager de fractionner la solution, les données à haute intégrité vivant dans votre firebase database relationnelle et les données dynamics vivant dans un magasin non relationnel.

Même si vous fournissez à un utilisateur l’ajout de colonnes personnalisées, les requêtes sur ces colonnes ne seront pas forcément efficaces. Il existe de nombreux aspects de la conception des requêtes qui leur permettent de bien fonctionner, le plus important étant la spécification correcte de ce qui doit être stocké en premier lieu. Ainsi, fondamentalement, est-ce que vous souhaitez permettre aux utilisateurs de créer un schéma sans réfléchir aux spécifications et être en mesure de dériver rapidement des informations de ce schéma? Si tel est le cas, il est peu probable qu’une telle solution évolue bien, surtout si vous souhaitez autoriser l’utilisateur à effectuer une parsing numérique des données.

Option 1

IMO cette approche vous donne un schéma sans savoir ce que signifie le schéma qui est une recette pour un désastre et un cauchemar pour les concepteurs de rapports. Par exemple, vous devez disposer des métadonnées pour savoir quelle colonne stocke quelles données. Si ces métadonnées sont gâchées, elles peuvent ralentir vos données. De plus, il est facile de placer les mauvaises données dans la mauvaise colonne. (“Quoi? Ssortingng1 contient le nom des couvents? Je pensais que c’était les drogues préférées de Chalie Sheen.”)

Option 3,4,5

OMI, les exigences 2, 3 et 4 éliminent toute variation d’un EAV. Si vous avez besoin d’interroger, de sortinger ou de faire des calculs sur ces données, un EAV est le rêve de Cthulhu et le cauchemar de votre équipe de développement et de votre DBA. EAV créera un goulot d’étranglement en termes de performances et ne vous donnera pas l’intégrité des données dont vous avez besoin pour accéder rapidement aux informations souhaitées. Les interrogations se tourneront rapidement vers les nœuds gordiens.

Option 2,6

Cela laisse vraiment un choix: rassembler les spécifications et ensuite construire le schéma.

Si le client souhaite obtenir les meilleures performances sur les données qu’il souhaite stocker, il doit passer par le processus de collaboration avec un développeur pour comprendre ses besoins afin de les stocker le plus efficacement possible. Il pourrait toujours être stocké dans une table distincte du rest des tables avec un code qui génère dynamicment un formulaire basé sur le schéma de la table. Si vous avez une firebase database qui autorise des propriétés étendues sur les colonnes, vous pouvez même les utiliser pour aider le générateur de formulaires à utiliser de jolis libellés, infobulles, etc., de manière à append le schéma. Dans les deux cas, pour créer et exécuter des rapports efficacement, les données doivent être stockées correctement. Si les données en question ont beaucoup de valeurs NULL, certaines bases de données peuvent stocker ce type d’informations. Par exemple, SQL Server 2008 comporte une fonctionnalité appelée Colonnes fragmentées spécifiquement pour les données contenant beaucoup de valeurs NULL.

Si ce n’était qu’un sac de données sur lequel aucune parsing, filtrage ou sorting ne devait être effectué, je dirais qu’une certaine variation d’un EAV pourrait faire l’affaire. Toutefois, compte tenu de vos besoins, la solution la plus efficace consistera à obtenir les spécifications appropriées, même si vous stockez ces nouvelles colonnes dans des tables distinctes et comstackz les formulaires de manière dynamic à partir de ces tables.

Colonnes rares

  1. Créez plusieurs tables UDF, une par type de données. Nous aurions donc des tables pour UDFSsortingngs, UDFDates, etc. Probablement faire la même chose que # 2 et générer automatiquement une vue quand un nouveau champ est ajouté

Selon mes recherches, plusieurs tableaux basés sur le type de données ne vous aideront pas dans les performances. Surtout si vous avez des données en bloc, comme des enregistrements 20K ou 25K avec plus de 50 UDF. La performance était la pire.

Vous devriez aller avec une seule table avec plusieurs colonnes comme:

 varchar Name varchar Type decimal NumberValue varchar SsortingngValue date DateValue 

Ceci est une situation problématique et aucune des solutions ne semble “correcte”. Cependant, l’option 1 est probablement la meilleure en termes de simplicité et de performances.

C’est également la solution utilisée dans certaines applications commerciales.

MODIFIER

Une autre option disponible maintenant, mais qui n’existait pas (ou du moins n’était pas mature) lorsque la question était posée à l’origine était d’utiliser les champs json dans la firebase database.

de nombreuses bases de données relationnelles prennent désormais en charge les champs basés sur json (qui peuvent inclure une liste dynamic de sous-champs) et autorisent l’interrogation de ces champs

postgress

mysql

J’ai eu de l’expérience ou 1, 3 et 4 et ils finissent tous soit en désordre, sans savoir clairement quelles sont les données ou vraiment compliquées avec une sorte de catégorisation en douceur pour diviser les données en types dynamics.

Je serais tenté d’essayer XML, vous devriez être capable d’imposer des schémas sur le contenu du fichier XML pour vérifier le typage des données, etc., ce qui aidera à conserver des ensembles de données UDF différents. Dans les nouvelles versions du serveur SQL, vous pouvez indexer sur des champs XML, ce qui devrait vous aider à améliorer les performances. (voir http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx ) par exemple

Si vous utilisez SQL Server, ne négligez pas le type sqlvariant. C’est assez rapide et devrait faire votre travail. D’autres bases de données peuvent avoir quelque chose de similaire.

Les types de données XML ne sont pas si bons pour des raisons de performances. Si vous effectuez des calculs sur le serveur, vous devez constamment les désérialiser.

L’option 1 sonne mal et semble brute, mais en termes de performance peut être votre meilleur pari. J’ai déjà créé des tables avec des colonnes appelées Field00-Field99, car vous ne pouvez tout simplement pas battre les performances. Vous devrez peut-être aussi prendre en compte votre performance INSERT, auquel cas c’est également celui qu’il vous faut. Vous pouvez toujours créer des vues sur cette table si vous voulez qu’elles soient propres!

Je l’ai très bien réussi dans le passé en utilisant aucune de ces options (option 6? :)).

Je crée un modèle avec lequel les utilisateurs peuvent jouer (stocker en tant que fichier XML et exposer via un outil de modélisation personnalisé) et à partir des tables et des vues générées par le modèle pour joindre les tables de base aux tables de données définies par l’utilisateur. Ainsi, chaque type aura une table de base avec des données de base et une table utilisateur avec des champs définis par l’utilisateur.

Prenons un document comme exemple: les champs typiques sont le nom, le type, la date, l’auteur, etc. Cela irait dans la table principale. Ensuite, les utilisateurs définiraient leurs propres types de documents spéciaux avec leurs propres champs, tels que contract_end_date, renouvellement_clause, blah blah blah. Pour ce document défini par l’utilisateur, il y aurait la table de document principale, la table xcontract, jointe sur une clé primaire commune (de sorte que la clé primaire xcontracts soit également étrangère à la clé primaire de la table principale). Ensuite, je générerais une vue pour envelopper ces deux tables. Performance lorsque l’interrogation était rapide. Des règles métier supplémentaires peuvent également être intégrées aux vues. Cela a très bien fonctionné pour moi.

SharePoint utilise l’option 1 et présente des performances raisonnables.

Dans les commentaires, je vous ai vu dire que les champs UDF doivent vider les données imscopes qui ne sont pas correctement mappées par l’utilisateur.

Une autre option consiste peut-être à suivre le nombre de fichiers UDF créés par chaque utilisateur et à les forcer à réutiliser des champs en indiquant qu’ils peuvent utiliser 6 (ou une autre limite tout aussi aléatoire) pour les champs personnalisés.

Lorsque vous rencontrez un problème de structuration de firebase database comme celui-ci, il est souvent préférable de revenir à la conception de base de l’application (système d’importation dans votre cas) et d’y append quelques ressortingctions supplémentaires.

Maintenant, ce que je ferais, c’est l’option 4 (EDIT) avec l’ajout d’un lien aux utilisateurs:

 general_data_table id ... udfs_linked_table id general_data_id udf_id udfs_table id name type owner_id --> Use this to filter for the current user and limit their UDFs ssortingng_link_id --> link table for ssortingng fields int_link_id type_link_id 

Assurez-vous maintenant de créer des vues pour optimiser les performances et obtenir vos index. Ce niveau de normalisation réduit l’encombrement de la firebase database, mais votre application est plus complexe.

Notre firebase database alimente une application SaaS (helpdesk software) où les utilisateurs disposent de plus de 7 000 “champs personnalisés”. Nous utilisons une approche combinée:

  1. (EntityID, FieldID, Value) pour rechercher les données
  2. un champ JSON dans la table des entities , qui contient toutes les valeurs d’entité, utilisé pour afficher les données. (De cette façon, vous n’avez pas besoin d’un million de JOIN pour obtenir les valeurs).

Vous pourriez en outre diviser # 1 pour avoir une “table par type de données” comme cette réponse le suggère, de cette façon vous pouvez même indexer vos UDF.

PS Quelques mots pour défendre l’approche “Entity-Atsortingbute-Value” que tout le monde continue de dénigrer. Nous avons utilisé # 1 sans # 2 pendant des décennies et cela a bien fonctionné. Parfois, c’est une décision commerciale. Avez-vous le temps de réécrire votre application et de redéfinir la firebase database, ou d’utiliser quelques dollars sur des serveurs cloud, qui sont vraiment bon marché de nos jours? En passant, lorsque nous utilisions l’approche n ° 1, notre firebase database contenait des millions d’entités, accessibles par des centaines de milliers d’utilisateurs, et un serveur de base dual-core de 16 Go fonctionnait très bien (vraiment une vm “r3” sur AWS) .