Conception de base de données dynamique des champs: une seule table ou plusieurs tables - de nombreux indices

- Je choisir la structure d'une base de données qui enregistre les types de contenu (par exemple. Les articles de Blog, Pages, Documents, Factures, devis, etc..) avec la dynamique des champs: par exemple, la Estimate type de contenu doit avoir les champs title, date et total price.

Toutefois, dans le temps, ces champs peuvent être ajoutés minerai supprimé, donc après 1 an, le Estimate contant type peut avoir la notes champ.

C'est une tâche commune prévue par le célèbre CMS (drupal par exemple), mais je me demande quelle est la meilleure approche pour avoir les meilleurs performances et de la flexibilité: Drupal, par exemple l'utiliser pour avoir une table avec basic champs (par exemple,title), et tous les champs secondaires sont stockés dans des sous-tableaux créés à la volée et lié à la principale avec les clés étrangères:

table node
| id | title         | ...
|  1 | First example |
table fields_node_total_price
| id | node_id | value  |
|  1 | 1       | 123.45 |
table fields_node_date
| id | node_id | value    |
|  1 | 1       | 12345677 |

etc..

Mon point de vue est que cette approche est très flexible mais facile de tomber dans le problème de performance: pour obtenir tous les champs pour un document, vous devez joindre les tables à de nombreuses reprises, et le code lui-même à la parcourir plusieurs fois pour construire la requête (mais cela ne devrais pas être un problème).

Btw multi-table est le plus utilisé.. donc doit avoir de nombreux inconvénients.

Im penser à ce genre de disvantages le fait d'utiliser une seule table:

| id | title | total_price | date | ec...

J'ai fait quelques tests avec 5 et 50 autres champs; les de performance entre l'unique approche de table et de la table multi-approche sont énormes: une seule table est d'environ 50x fois plus rapide.

Chaque fois qu'un champ est ajouté, une colonne est ajoutée à la table.. ce genre de problèmes que cette approche hausse?

MODIFIER

Permettez-moi d'apporter quelques précisions:

L'application est encore en phase de conception, est une refonte complète d'une application ancienne où les champs de numéros étaient statiques
Nous avons fait quelques essais de simulation d'un objet à stocker, à la fois avec une seule approche de table de table et multi approche (à l'aide de 50 champs), les résultats sont les suivants:

Temps en secondes:

Test                                                            1°          2°          3°          4°          5°          avg
1000 insert single_table                                        8,5687      8,6832      8,7143      8,7977      8,6906      8,69090137389466
1000 select single table LIKE '%key%' on char(250) field        1,5539      1,5540      1,5591      1,5602      1,5564      1,556705142
1000 select single table LIKE '%key%' on char(25) field         0,8848      0,8923      0,8894      0,8919      0,8888      0,889427996
1000 select single table id = $n                                0,2645      0,2620      0,2645      0,2632      0,2636      0,263564462
1000 select single table integer field < $j                     0,8627      0,8759      0,8673      0,8713      0,8767      0,870787334
1000 insert multi_table                                         446,3830    445,2843    440,8151    436,6051    446,0302    443,023531816
1000 select multi table LIKE '%key%' on char(250) field         1,7048      1,6822      1,6817      1,7041      1,6840      1,691367196
1000 select multi table LIKE '%key%' on char(25) field          0,9391      0,9365      0,9382      0,9431      0,9408      0,939536426
1000 select multi table id = $n                                 0,9336      0,9287      0,9349      0,9331      0,9428      0,93460784
1000 select multi table integer field < $j                      2,3366      2,3260      2,3134      2,3342      2,3228      2,326600456

"vous devez interroger la base de données à de nombreuses reprises" - euh, non. Vous feriez construire une seule requête qui joint à chacune des tables que vous voulez récupérer des données. "le code lui-même à la parcourir de nombreuses fois" - encore une fois, non, sauf si vous faites quelque chose de bizarre.
vous êtes à la droite; avec query the db many times je veux dire même construire une requête avec plusieurs jointures, qui en général pourrait entraîner le rendement lent
peut-être que cette question était un meilleur ajustement pour dba.stackexchange.com ?

OriginalL'auteur Strae | 2012-01-10

5

Il peut être la peine d'étudier ce qui est possible avec les bases de données NoSQL. Je n'ai pas utilisé beaucoup de moi-même, mais étant donné que vous dites qu'il faut "...stocker les types de contenu (par exemple. Les articles de Blog, Pages, Documents, Factures, devis, etc..) avec la dynamique des champs", il me semble que si il peut être une approche raisonnable.

De la Article de Wikipedia;

...Ces magasins de données ne peut pas exiger fixe schémas de table, généralement
éviter les opérations de jointure, et en général, l'échelle horizontale.

et

Souvent, les bases de données NoSQL sont classés en fonction du mode de stockage des
les données et il tombe dans des catégories comme la Clé-Valeur des magasins,
BigTable Implémentations, Document-Magasin bases de données et le Graphique
La base de données.

Je ne dis pas que c'est la réponse à tous vos problèmes, mais je serais certainement dire qu'il vaut la peine de regarder.

En ce qui concerne d'autres approches, j'ai utilisé de l'Entité-Attribut-Valeur (VAE) dans le passé, et bien que la performance probablement à la traîne derrière d'avoir un schéma fixe, je pense que c'est un compromis qui avait être fait pour permettre la flexibilité dans le schéma.

Ma situation est susceptible de différer de la vôtre, mais je vais jeter pour vous, dans le cas où il est d'aucune aide. Nous avons cassé la structure de la table en quelque chose de logique pour notre situation. Il y a un peu d'une hiérarchie naturelle en ce sens qu'il est un parent de la table, que la plupart des autres tableau se rapportent.

Même si nous avions besoin de structure dynamique en raison de la variété des données que nous traitons, il y a aussi une structure fixe. Par conséquent, pour chaque table nécessitant une structure dynamique, nous avons créé un "principal" de la table, et un "attribut" de la table.

Un exemple de ce (spécifique à SQL Server) peut être vu ci-dessous;
```
CREATE TABLE [dbo].[ParentTbl](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [KnownCol1] [real] NOT NULL,
        -- Lots of other columns ommitted
    [KnownColn] [real] NULL
)        

CREATE TABLE [dbo].[MainTbl](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NOT NULL, -- FK to ParentTbl.Id
    [KnownCol1] [real] NOT NULL,
        -- Lots of other columns ommitted
    [KnownColn] [real] NULL
) 

CREATE TABLE [dbo].[MainTblAttr](
    [Id] [bigint] IDENTITY(1,1) NOT NULL, -- Note big int to cater for LOTS of records
    [MainId] [int] NOT NULL, --FK to MainTbl.Id
    [AttributeColumn] [nvarchar](255) NOT NULL,
    [AttributeValue] [nvarchar](max) NOT NULL
)
```
Vous pouvez ensuite effectuer un PIVOT de la requête pour l'aider à récupérer vos données. Donné, vous aurez différents attributs que vous devez déterminer les colonnes à inclure dans le pivot. J'ai trouvé cet exemple précieux quand je développais ma solution. Cependant, il y a des tas d'exemples sur. Il suffit de chercher pour pivot de colonnes dynamiques.

Dans mon exemple, demander à un parent de table est d'une grande aide à limiter la quantité de données que j'ai besoin de chalut à travers les limites des enregistrements de l'enfant que j'ai besoin de regarder. Cela pourrait ne pas être si dans votre cas, mais j'espère que cela va vous donner quelques idées.

Meilleur de la chance.

Je voudrais rester à l'écart de NoSQL, sauf si vous SAVEZ vous en avez besoin. À moins que vous sont google, amazon, etc. Je ne le crois pas. La grande force de NoSQL, c'est qu'ils peuvent faire face à beaucoup de gros volumes de données que les traditionnels de bases de données relationnelles. Toutefois, cela se fait au détriment de la facilité d'utilisation d'une base de données relationnelle: par exemple, pas de descriptif, les langages de requête, de moins en moins de garanties en matière de cohérence des données, etc. - En bref, ils peuvent être incroyablement efficace si vous traitez avec des quantités massives de données, mais sont difficiles à utiliser dans la comparaison. Pour tirer parti de l'efficacité dont vous avez besoin d'une bonne taille de cluster pour les exécuter.

OriginalL'auteur Mr Moose
3

Il n'y a pas une seule "bonne" réponse à cette question. Comme vous l'avez déjà mentionné, il arrive à un compromis entre la souplesse et la vitesse.

Cela dépend de ce que le goulot d'étranglement dans votre application. Avez-vous fait de profilage sur votre application? Une base de données d'une requête pertinente en rapport avec typique de l'utilisateur final de ping-temps, la vitesse de transfert, etc? Il n'y a pas vraiment de point se soucier de l'optimisation de la performance jusqu'à ce que vous êtes sûr que vous avez réellement un problème de performance ET de savoir où le goulot d'étranglement est!

J'aime utiliser firebug sur Firefox pour le moment combien de temps ma page d'affichage avec l'utilisateur final et de le comparer à la suite d'un chronomètre commencé avant la requête et s'est arrêté après la requête. Pour la facilité d'utilisation, je l'imprimer au bas de chaque page lors de profilage.

Avez-vous considéré les points de vue de compenser les désavantages de la multi-approche de table?

Concernant la complexité de la requête problème: "virtuel" des vues, vous pouvez éviter d'avoir à utiliser compliqué rejoint dans votre chaque jour des requêtes. Vous mettez le rejoint dans la définition de la vue et sur la modification de la dynamique des champs vous n'avez qu'à ajuster la vue. (Remarque: avec vue virtuelle de votre "simple" requête dynamique est réécrite en utilisant les jointures dans la définition de la vue.)

Concernant le problème de vitesse: vous pouvez utiliser "matérialisée" définitions de vue avec le multi-approche de table pour avoir des performances de la table. Avec des vues matérialisées le SGBD crée une table physique à l'aide de la définition de la vue en utilisant les jointures dans la définition de la vue. Le résultat est que vous êtes vraiment l'interrogation d'une "table unique" - qui cependant n'est maintenu automatiquement synchronisés avec votre multi-définition de la table. Vous obtenez le meilleur des deux mondes au détriment de la DB de l'espace de stockage.

En fonction de votre SGBD, vous pouvez également mettre à jour les points de vue directement (plutôt que le multi-table). Je crois que c'est le cas avec MySQL. Avec Postgres vous avez besoin d'utiliser des déclencheurs pour indiquer au système comment modifier le sous-jacent multi-table.

En Résumé:
1. Personnellement, si j'ai voulu créer un système pour le dernier, je voudrais aller avec
  le multi-approche de table avec virtualisé points de vue. Je voudrais ensuite
  "matérialiser" seuls ceux vues sur lesquelles j'ai l'impression que la performance est
  en manque. C'est plus d'efforts pour sortir le sol en cours d'exécution à
  table unique de vitesse, mais Il restera incroyablement flexible.
2. Si je voulais quelque chose de rapide et sale mais rapide, j'irais avec une seule table. - Mais parfois, il peut être une douleur dans le mais d'intégrer certaines modifications. Je ne vois pas un problème découlant d'avoir un grand nombre de colonnes. Tout SGBD relationnel devrait être ok.
3. Si je voulais quelque chose de rapide et sale, mais souple, je voudrais aller avec multi-table et vous inquiétez pas sur la définition de points de vue & déclencheurs, mais seulement de définir des index pour accélérer les opérations de jointure.
Dernier point:
Vous devriez vraiment essayer de faire autant de traitement de données que possible dans le SGBD. (c'est à dire avec la requête), Vous avez déjà compris que "le code lui-même à la parcourir plusieurs fois pour construire la requête" il n'est pas vrai (voir affichage, etc.). Toutefois, il suggère que vous avez une tendance à faire trop de traitement de données dans votre application. SQL est incroyablement expressif et votre base de données sera très probablement une utilisation beaucoup plus efficace des algorithmes pour évaluer votre traitement de données que vous êtes susceptible de mettre en œuvre vous-même. Remarque: une requête SQL qui semble incroyablement complexe peut en effet courir très vite!

Ainsi, si vous faites autre chose que simplement en parcourant votre résultat de la requête pour afficher une page web, vous pouvez probablement encore mettre plus de la logique dans votre requête.

Have you done some profiling on your application? mmh non, l'ensemble de l'application (db, serveur, code) est encore en phase de conception. Nous avons fait quelques essais de simulation de la table simples et multi-structures avec des données fausses, et les résultats montrent que seule la table est le 4997% plus rapide que la table multi-insert, 8% plus rapide avec select ... like '%key%' on a big field, 5% faster with comme "select ... %des% sur un petit terrain, 254% plus vite avec select ... id = X' and 167% faster with sélectionnez ... integer_field < y'.
Il se pourrait bien que la requête sera insignifiant dans le grand schéma des choses: par exemple, si votre requête ne prend que 0,01 ms au lieu de 1 ms à exécuter si la page web que vous affichez à votre utilisateur avec le résultat prend la moyenne PC 500 ms pour le rendu? Votre temps aurait été mieux dépensé à essayer de nettoyer vos modèles html. - Je voudrais aller avec la mise en œuvre qui est le plus pratique à utiliser. Si il s'avère à être vraiment trop lent, il ne devrait pas être trop difficile de changer la DB structure dorsale plus tard, à condition que vous utilisez des couches d'abstraction dans votre code (par exemple, points de vue).
+ 1 pour Note: a SQL query that looks incredibly complex may in fact run very fast! aussi, dans l'ensemble j'ai aimé vous répondre.. spécialement le materialized

OriginalL'auteur ARF
0

La première solution est une "valeur de l'attribut" de la base de données : Entité la Valeur de l'Attribut de la Base de données vs stricte du Modèle Relationnel de commerce électronique

J'irais pour le plus tard solution : base de données pour stocker des données structure pas ! Nous avons problème majeur parce que nous avons une entité de la valeur de l'attribut de base de données dans lequel on peut insérer n'importe quel type de données, mais il est impossible de les interroger ou à la cible spécifique des données sans la magie des cordes.

Ou vous pouvez faire une autre solution : stocker votre bateau champ dans une version sérialisée de votre AdditionnalFields objet.

OriginalL'auteur remi bourgarel
0

Dans les grands systèmes (50+ colonnes à l'aide de la réplication avec 5+ hôtes) la charge liée à l'actualisation d'une seule ligne dans une table augmente lors de l'ajout de colonnes supplémentaires (colombie-britannique. l'ensemble de la ligne doit être répliqués). Cet effet peut être réduit par la division d'un grand tableau en plusieurs parties. Lors de l'utilisation d'index appropriés, ce qui vient à moindre coût de l'analyse des charges de travail. Bien qu'il altère les performances de plaquettes.

OriginalL'auteur mschneider

Vous devez vous connecter pour publier un commentaire.