Est-ce que stocker une liste délimitée dans une colonne de base de données est si mauvais?
Imaginez un formulaire web avec un ensemble de cases à cocher (une ou de la totalité d'entre elles peuvent être sélectionnés). J'ai choisi de les sauvegarder dans une liste de valeurs séparées par des virgules et stockées dans une colonne de la table de la base de données.
maintenant, je sais que la bonne solution serait de créer une deuxième table et normaliser correctement la base de données. Il a été plus rapide de mettre en œuvre la solution facile, et je voulais avoir une preuve de concept de cette application rapidement et sans avoir à y consacrer trop de temps.
j'ai pensé que le temps gagné et le code plus simple en valait la peine dans ma situation, est-ce un choix de conception défendable, ou aurais-je dû le normaliser dès le début?
un peu plus de contexte, il s'agit d'une petite application interne qui remplace essentiellement un fichier Excel qui a été stocké dans un dossier partagé. Je demande aussi parce que je pense à nettoyer le programme et le rendre plus soutenable. Il y a des choses là je ne suis pas entièrement satisfait avec, l'un d'eux est le sujet de cette question.
9 réponses
en plus de violer première forme normale en raison de la répétition du groupe de valeurs stockées dans une seule colonne, les listes séparées par des virgules ont beaucoup d'autres problèmes plus pratiques:
- ne Peut pas garantir que chaque valeur est le bon type de données: aucun moyen de l'empêcher 1,2,3,de la banane,5
- ne peut pas utiliser de contraintes de clé étrangère pour lier des valeurs à une table de recherche; aucun moyen de forcer le référentiel intégrité.
- ne Peut pas appliquer l'unicité: aucun moyen de l'empêcher 1,2,3,3,3,5
- ne peut supprimer une valeur de la liste sans récupérer la liste entière.
- ne peut pas stocker une liste plus longue que ce qui correspond à la colonne string.
- difficile de rechercher toutes les entités avec une valeur donnée dans la liste; vous devez utiliser un scan de table inefficace. Peut avoir recours à des expressions régulières, par exemple dans MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'* - difficile de compter les éléments de la liste, ou de faire d'autres requêtes agrégées.
- difficile de joindre les valeurs à la table de recherche.
- difficile de trouver la liste dans l'ordre.
- stocker des entiers en tant que chaînes prend environ deux fois plus d'espace que stocker des entiers binaires. Sans parler de la place prise par les virgules.
pour résoudre ces problèmes, vous devez écrire des tonnes de code d'application, réinventant la fonctionnalité que le RDBMS fournit déjà beaucoup plus efficacement .
les listes séparées par des virgules sont tellement erronées que j'en ai fait le premier chapitre de mon livre: SQL Antipatterns: Avoiding the Pitfalls of Database Programming .
il y a des moments où vous devez employer dénormalisation, mais comme @OMG Ponies mentionne , ce sont des cas exceptionnels. Toute "optimisation" non relationnelle profite à un type de requête au détriment d'autres utilisations des données, alors assurez-vous de savoir lesquelles de vos requêtes doivent être traitées afin qu'elles méritent une dénormalisation.
* MySQL 8.0 ne supporte plus cette syntaxe d'expression de limite de mots.
il y a de nombreuses questions à se poser:
- comment obtenir un nombre de valeurs spécifiques à partir de la liste séparée par des virgules
- comment obtenir les enregistrements qui n'ont que la même 2/3/etc spécifique de la valeur à partir de cette liste séparée par des virgules
un autre problème avec la liste séparée par des virgules est de s'assurer que les valeurs sont cohérentes - le stockage de texte signifie la possibilité de fautes de frappe...
ce sont tous les symptômes de données dénormalisées, et soulignent pourquoi vous devriez toujours modéliser pour des données normalisées. La dénormalisation peut être une optimisation de requête, à appliquer lorsque le besoin se présente effectivement .
"Une des raisons était de la paresse".
cela sonne l'alarme. La seule raison pour laquelle vous devriez faire quelque chose comme cela, c'est que vous savez comment le faire "le bien" mais vous êtes venus à la conclusion qu'il est tangible raison de ne pas le faire de cette façon.
ayant dit ceci: si les données que vous choisissez de stocker de cette façon sont des données que vous n'aurez jamais besoin d'interroger, alors il peut y avoir un cas pour les stocker de la manière que vous avez choisi.
(certains utilisateurs contesteraient la déclaration de mon paragraphe précédent, disant que"vous ne pouvez jamais savoir quelles exigences seront ajoutées à l'avenir". Ces utilisateurs sont soit erronées ou indiquant une conviction religieuse. Il est parfois avantageux de travail aux besoins que vous avez devant vous.)
en général, tout peut être défendable s'il répond aux exigences de votre projet. Cela ne signifie pas que les gens seront d'accord avec ou voulez défendre votre décision...
en général, stocker des données de cette façon est sous-optimal (par exemple plus difficile de faire des requêtes efficaces) et peut causer des problèmes de maintenance si vous modifiez les éléments dans votre forme. Peut-être auriez-vous pu trouver un terrain du milieu et utiliser un entier représentant un ensemble de drapeaux de bits à la place?
Oui, je dirais que c'est vraiment mauvais. C'est un choix défendable, mais ce n'en est pas correcte ou bonne.
il casse la première forme normale.
une deuxième critique est que mettre des résultats d'entrée bruts directement dans une base de données, sans aucune validation ou liaison du tout, vous laisse ouvert aux attaques par injection SQL.
ce que vous appelez la paresse et le manque de connaissance du SQL est la matière dont les néophytes sont faits. Je vous recommande de prendre le temps de le faire correctement et de le voir comme une occasion d'apprendre.
ou laissez-le tel quel et apprenez la douloureuse leçon d'une attaque par injection SQL.
j'avais besoin d'une colonne multi-valeurs, elle pourrait être implémentée comme un champ xml
Il pourrait être converti en délimité par des virgules tant que de besoin
interrogation d'une liste XML dans sql server en utilisant Xquery .
en étant un champ xml, certaines des préoccupations peuvent être résolues.
avec CSV: ne peut pas garantir que chaque valeur est le bon type de données: aucun moyen d'empêcher 1,2, 3, banane, 5
avec XML: les valeurs dans une étiquette peuvent être forcées d'être le type correct
avec CSV: ne peut pas utiliser de contraintes de clé étrangère pour lier des valeurs à une table de recherche; aucun moyen de renforcer l'intégrité référentielle.
avec XML: toujours un problème
avec CSV: ne Peut pas appliquer l'unicité: aucun moyen de prévenir la 1,2,3,3,3,5
avec XML: toujours un problème
avec CSV: ne peut pas supprimer une valeur de la liste sans récupérer la liste entière.
avec XML: les éléments simples peuvent être supprimés
avec CSV: Difficile de rechercher toutes les entités avec une valeur donnée dans la liste; vous devez utiliser un scan de table inefficace.
avec XML: le champ xml peut être indexé
avec CSV: difficile de compter les éléments dans la liste, ou de faire d'autres requêtes agrégées.**
avec XML: pas particulièrement dur
avec CSV: difficile de joindre les valeurs à la table de recherche de référence.**
avec XML: pas particulièrement dur
avec CSV: difficile de récupérer la liste dans l'ordre.
avec XML: pas particulièrement dur
avec CSV: stocker des entiers en tant que chaînes prend environ deux fois plus d'espace que stocker des entiers binaires.
avec XML: le stockage est encore pire qu'un csv
avec CSV: Plus beaucoup de caractères virgule.
avec XML: les balises sont utilisées à la place des virgules
en bref, en utilisant XML permet de contourner certains problèmes avec la liste délimitée et peut être converti en une liste délimitée au besoin
Oui, c' est que mauvais. Mon point de vue est que si vous n'aimez pas utiliser des bases de données relationnelles alors chercher une alternative qui vous convient mieux, Il ya beaucoup de projets "NOSQL" intéressants là-bas avec quelques fonctionnalités vraiment avancées.
J'utilise depuis plus de 4 ans une liste d'onglets séparés par paire clé/valeur dans une colonne NTEXT dans SQL Server et cela fonctionne. Vous ne perdez pas la flexibilité de faire des requêtes mais d'un autre côté, si vous avez une bibliothèque qui persiste/derpersists la paire de valeurs clés alors ce n'est pas une si mauvaise idée.
je prendrais probablement le terrain du milieu: faire chaque champ dans le CSV dans une colonne séparée dans la base de données, mais pas beaucoup de souci au sujet de la normalisation (au moins pour l'instant). À un certain point, la normalisation pourrait devenir intéressant, mais avec toutes les données enfoncées dans une colonne unique, vous gagnez pratiquement aucun avantage de l'utilisation d'une base de données du tout. Vous devez séparer les données en champs/colonnes logiques/peu importe le nom que vous voulez leur donner avant de pouvoir les manipuler. de façon significative à tous.