Paires clé-valeur dans la base de données relationnelle

Question

Quelqu'un a de l'expérience avec le stockage de paires clé-valeur dans une base de données?

J'ai été en utilisant ce type de table:

CREATE TABLE key_value_pairs ( 
    itemid           varchar(32) NOT NULL,
    itemkey         varchar(32) NOT NULL,
    itemvalue       varchar(32) NOT NULL,
    CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey)
)

Alors, par exemple les lignes suivantes peuvent exister:

 itemid            itemkey        itemvalue    
 ----------------  -------------  ------------ 
 123               Colour         Red            
 123               Size           Medium             
 123               Fabric         Cotton

Le problème avec ce système est la syntaxe SQL nécessaire à l'extraction de données est assez complexe. Ne serait-il pas mieux de créer une série de clés/valeurs des colonnes?

CREATE TABLE key_value_pairs ( 
    itemid            varchar(32) NOT NULL,
    itemkey1        varchar(32) NOT NULL,
    itemvalue1      varchar(32) NOT NULL,
    itemkey2        varchar(32) NOT NULL,
    itemvalue2      varchar(32) NOT NULL,
 . . .etc . . .
)

Ce sera plus facile et plus rapide de requête mais manque d'extensibilité de la première approche. Tous les conseils?

Answer 1

Avant de continuer sur votre approche, je suggère humblement vous prenez du recul et demandez-vous si vous voulez vraiment pour stocker ces données dans une "Paire Clé-Valeur"de la table. Je ne sais pas, mais mon expérience m'a montré que chaque fois que j'ai fait ce que vous faites, plus tard, je souhaite que je l'avais créé une table de couleur, un tissu de table et une table de taille.

Pensez à des contraintes d'intégrité référentielle, si vous prenez la paire clé-valeur approche, la base de données ne peut pas vous dire quand vous essayez de stocker une couleur id dans un champ de taille

Pensez aux avantages de performance de le rejoindre sur une table avec 10 valeurs par rapport à une valeur générique qui peut avoir des milliers de valeurs dans plusieurs domaines. Quelle est l'utilité d'un index sur la Valeur de Clé de vraiment être?

Généralement le raisonnement derrière de faire ce que tu fais, c'est parce que les domaines doivent être "défini par l'utilisateur". Si c'est le cas, alors même que je ne vais pas vous pousser vers la création de tableaux à la volée (même si ce n'est une approche possible).

Toutefois, si votre raisonnement est parce que vous pensez qu'il sera plus facile à gérer que plusieurs tables, ou parce que vous êtes prévoyant un entretien de l'interface utilisateur qui est générique pour tous les domaines, puis de s'arrêter et de penser vraiment dur avant de continuer.

Answer 2

Il existe une autre solution qui se situe quelque part entre les deux. Vous pouvez utiliser une colonne de type XML pour les clés et les valeurs. Ainsi, vous conservez le champ itemid, puis un champ xml contenant le xml défini pour certaines paires de valeurs de clé, telles que <items> <item key="colour" value="red"/><item key="xxx" value="blah"/></items> Ensuite, lorsque vous extrayez vos données de la base de données, vous pouvez traiter le xml de différentes manières. . En fonction de votre utilisation. C'est une solution extensible.

Answer 3

Dans la plupart des cas, vous utilisez la première méthode, c'est parce que vous n'avez pas vraiment s'assit et de la pensée de votre modèle.. "eh Bien, nous ne savons pas ce que les touches seront encore". En général c'est assez mauvaise conception. Ça va être plus lent que le fait d'avoir vos clés sous forme de colonnes, de laquelle ils devraient être.

Je voudrais aussi demander pourquoi votre id est un varchar.

Dans les rares cas où vous devez vraiment mettre en œuvre une clé/valeur de la table, la première solution est parfaite, bien que, j'avais veulent généralement d'avoir les clés dans un tableau distinct de sorte que vous ne sont pas stocker varchars que les clés de votre clé/valeur de la table.

par exemple,

CREATE TABLE valid_keys ( 
    id            NUMBER(10) NOT NULL,
    description   varchar(32) NOT NULL,
    CONSTRAINT pk_valid_keys PRIMARY KEY(id)
);

CREATE TABLE item_values ( 
    item_id NUMBER(10) NOT NULL,
    key_id  NUMBER(10) NOT NULL,
    item_value VARCHAR2(32) NOT NULL,
    CONSTRAINT pk_item_values PRIMARY KEY(id),
    CONSTRAINT fk_item_values_iv FOREIGN KEY (key_id) REFERENCES valid_keys (id)
);

Ensuite, vous pouvez même aller à la noix et ajouter un "TYPE" pour les clés, permettant à certains de vérification de type.

Answer 4

Une fois, j'ai utilisé des paires clé-valeur dans une base de données dans le but de créer une feuille de calcul (utilisé pour la saisie des données) dans lequel un caissier serait la synthèse de son activité de travail d'un tiroir-caisse. Chaque k/v de la paire représente une cellule nommée dans lequel l'utilisateur a entré une valeur monétaire. La raison principale de cette approche est que la feuille de calcul a été fortement sujette à changement. De nouveaux produits et services ont été ajoutés régulièrement (donc de nouvelles cellules apparaissent). Aussi, certaines cellules n'étaient pas nécessaires dans certaines situations et pourrait être supprimé.

L'application que j'ai écrit était une réécriture d'une application qui ne pause le narrateur feuille dans des sections séparées chacune représentée dans une autre table. Le problème ici est que, puisque les produits et services ont été ajoutés, schéma de modifications ont été nécessaires à. Comme avec tous les choix de conception il y a des avantages et des inconvénients à prendre une certaine direction par rapport à l'autre. Mon refonte certainement effectué plus lent et plus rapidement consommé de l'espace disque; cependant, il était très agile et a permis pour de nouveaux produits et services à ajouter en quelques minutes. Le seul problème à noter, cependant, a été disque de consommation; il n'y avait pas d'autres maux de tête que je me rappelle.

Comme déjà mentionné, la raison pour laquelle je considère habituellement comme une paire clé-valeur approche est lorsque les utilisateurs-ce qui pourrait être un propriétaire de l'entreprise-veulent créer leurs propres types ayant un spécifique à l'utilisateur un ensemble d'attributs. Dans de telles situations, je suis venu à la suite de la détermination.

Si il n'y a pas besoin de récupérer des données par ces attributs ou de la recherche peut être reporté à l'application une fois qu'un bloc de données a été récupérée, je vous recommandons de conserver tous les attributs d'un champ de texte (à l'aide de JSON, YAML, XML, etc.). Si il y a un fort besoin de récupérer des données par ces attributs, il devient malpropre.

Vous pouvez créer un "attributs" de la table (id, item_id, clé, valeur, data_type, sort_value) où la colonne de tri convertit la valeur réelle dans une chaîne-triables représentation. (par exemple, la date: "2010-12-25 12:00:00", numéro de: "0000000001"), Ou vous pouvez créer des tables d'attributs par type de données (par exemple, string_attributes, date_attributes, number_attributes). Parmi les nombreux avantages et les inconvénients de ces deux approches: la première est la plus simple, la seconde est plus rapide. Les deux vont vous amener à écrire des laids, des requêtes complexes.

Answer 5

Par expérience, j'ai constaté que certaines touches seront plus largement utilisés ou demandés le plus souvent. Nous avons alors légèrement de normalisation de la conception d'inclure un champ spécifique de retour dans le principal "point" de la table.

par exemple. si chaque Élément a une Couleur, vous pouvez ajouter de la Couleur de la colonne de votre tableau des éléments. Le tissu et la Taille peuvent être utilisés moins souvent et peuvent être séparés dans la paire clé-valeur table. Vous pouvez même garder la couleur de la paire clé-valeur table, mais dupliquer les données dans l'élément de la table pour obtenir les avantages de performance.

Évidemment, cela varie en fonction des données et le degré de souplesse dont vous avez besoin les paires clé-valeur. Il peut également entraîner vos données d'attribut n'étant pas situé consistantly. Cependant, de normaliser ne simplifient considérablement les requêtes et améliore leurs performances.

J'avais l'habitude de considérer seulement de normaliser lorsque la performance est question, non seulement de simplifier une requête.