Structure de base de données pour une application de type enquête

Question

Cette question est très similaire à la question précédente Conception d'une base de données pour une enquête , Cependant, l'auteur de la question n'a pas précisé la quantité de données qu'il traitait, le type de données et ce qu'il devait en faire, et je pense que cela a beaucoup d'importance.

J'ai donc été chargé d'ajouter une fonction de sondage à une application. L'application doit gérer 50 organisations distinctes, chaque organisation ayant jusqu'à 500 enquêtes. Chaque enquête comprendra jusqu'à 150 questions et stockera des données allant du vrai/faux, des dates, etc. jusqu'à des paragraphes de texte. Il sera possible de répondre jusqu'à 10 000 fois à chaque enquête.

Je vois trois façons principales de concevoir la base de données pour répondre à ce besoin.

1. Un seul tableau pour toutes les questions, et un autre pour toutes les réponses, par exemple.

Table des questions : [ survey_id, question ] etc

Table des réponses : [ question_id, answer]

2. Un tableau pour chaque enquête, avec un champ pour chaque question

3. Une base de données pour chaque client... (c'est un joker lancé par un collègue, je suis très sceptique à ce sujet)

Bien que j'aime l'idée de l'option 1, il y a quelques problèmes. Nous stockerons jusqu'à 38 milliards de lignes, le champ de réponse devra être un champ de texte, ce qui rendra les requêtes et le tri par date, par exemple, très lents. Des rapports en temps réel sont attendus avec cette application.

Compte tenu de la quantité de données et des exigences en matière de rapports, je me sens obligé d'envisager de créer les tables de manière dynamique. Les données ne sont pas sujettes à modification une fois créées en raison du champ dans lequel elles sont utilisées ; si un changement est nécessaire, tout est supprimé et recommencé ; je ne crains donc pas d'avoir à effectuer des mises à jour de schéma en cours d'enquête. Le principal problème que je vois est le nombre de tables : 25 000 tables, c'est beaucoup, et je ne suis pas sûr que ce soit mieux que d'interroger 38 milliards de lignes de données mal structurées, ni même qu'il y ait des limites à ne pas dépasser. Le seul avantage est que nous pouvons être sûrs à 100% qu'il n'y aura pas de jointures de tables et qu'il est peu probable que plus de 500 tables différentes soient évaluées en une journée.

Je ne suis pas sûr du fonctionnement interne de MySql (la base de données actuellement utilisée par le client), mais je ne pense pas que le fait de la diviser en plusieurs bases de données fasse beaucoup de différences sur le même serveur ? Cela dit, j'ai la possibilité d'utiliser la base de données de mon choix.

Quelle est la meilleure approche dans ce scénario et existe-t-il une quatrième option que je n'ai pas envisagée ?

Answer 1

Vous pourriez conserver les réponses aux paragraphes dans un tableau séparé, en raison de leur nature distincte des autres types de questions qui auront des valeurs minuscules. Vous aurez besoin d'un champ de type de question (ou de type de réponse ?) pour cela.

Les questions sont-elles totalement uniques d'un client à l'autre et d'une enquête à l'autre, ou ne seront-elles qu'une répétition de "questions similaires" avec des valeurs différentes ? Dans ce dernier cas, la génération de questions peut être rendue dynamique.

Les idées ('table pour chaque enquête'), ('champ pour chaque question') et ('une base de données pour chaque client') me semblent trop dénormales. Une table de liaison enquête-question devrait suffire. De même, vous pourriez simplement avoir une table Clients et avoir une table de liens entre les clients et les enquêtes.

Les éléments de données qui sont nécessaires à l'établissement de rapports en temps réel peuvent être conservés dans d'autres tableaux conçus séparément pour les informations résumées. Bien entendu, si les valeurs des questions-réponses changent après la mise à jour des tableaux récapitulatifs, ceux-ci doivent être remis à jour pour refléter les valeurs récapitulatives modifiées.

De cette façon, vous ne vous heurterez à aucune limite. La base de données peut être optimisée pour la production de rapports en temps réel, en disposant de tables supplémentaires uniquement pour les éléments de rapport ou les comptages. Vous pouvez également disposer d'une base de données supplémentaire, de type EIS, pour le reporting en temps réel, afin que les mises à jour des transactions n'entravent pas le reporting et vice versa.

Je suis bien conscient que cette réponse vient de l'idée que "la normalisation peut toujours fonctionner assez bien" pour ce scénario, je ne vois pas pourquoi ce ne serait pas le cas.

Answer 2

Donc, en supposant que votre plus grande préoccupation est la performance et que vous savez réellement quel type de données doit être la réponse, j'opterais pour option #1 mais avec des colonnes spécialisées supplémentaires.

Gardez toutes les réponses dans un seul tableau, dans une colonne générale, mais ajoutez des colonnes supplémentaires spécifiques à chaque type, par exemple. a_date , a_bool . Remplissez seulement la colonne générale et la colonne avec le type de données que cette réponse devrait être. Le nombre de types est très limité et vous pourrez définir des index sur chacun d'entre eux et tirer parti de leur type. L'avantage est également que vous stockez des informations sur le type de réponse et que vous pouvez facilement vous concentrer sur un type de réponse particulier, par exemple les dates, ce qui peut être utile pour les rapports.

Vous pouvez également séparer complètement les types dans le tableau et, au lieu d'avoir toutes les réponses dans une colonne générale et une colonne supplémentaire par type, vous pouvez en avoir une pour le texte, une pour les dates, etc. De cette façon, vous devriez être en mesure de traiter les réponses ouvertes (texte) et de réduire la redondance des données en une seule étape.

Bonus : Pour un gain de performance, je regarderais également le partitionnement de la base de données, par exemple en utilisant chaque client (en supposant que le reporting sera par client).