Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Key value pairs in relational database

Tags:

sql

database

Does someone have experience with storing key-value pairs in a database?

I've been using this type of 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) ) 

Then for example the following rows could exist:

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

The trouble with this scheme is the SQL syntax required to extract data is quite complex. Would it be better to just create a series of key/value columns?

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 . . . ) 

This will be easier and faster to query but lacks the extensibility of the first approach. Any advice?

like image 689
horace Avatar asked Sep 24 '08 09:09

horace


People also ask

How are key-value pairs stored in a database?

I think the best way to design such tables is as follows: Make the frequently used fields as columns in the database. Provide a Misc column which contains a dictionary(in JSON/XML/other string formeat) which will contain the fields as key-value pairs.

How is a key-value pair database different from a relational database?

Unlike relational databases, key-value databases do not have a specified structure. Relational databases store data in tables where each column has an assigned data type. Key-value databases are a collection of key-value pairs that are stored as individual records and do not have a predefined data structure.

What is key-value database example?

A key-value pair is the fundamental data structure of a key-value store or key-value database, but key-value pairs have existed outside of software for much longer. A telephone directory is a good example, where the key is the person or business name, and the value is the phone number.

Which data type key-value pairs are used?

A key-value pair (KVP) is an abstract data type that includes a group of key identifiers and a set of associated values. Key-value pairs are frequently used in lookup tables, hash tables and configuration files.


1 Answers

Before you continue on your approach, I would humbly suggest you step back and consider if you really want to store this data in a "Key-Value Pair"table. I don't know your application but my experience has shown that every time I have done what you are doing, later on I wish I had created a color table, a fabric table and a size table.

Think about referential integrity constraints, if you take the key-value pair approach, the database can't tell you when you are trying to store a color id in a size field

Think about the performance benefits of joining on a table with 10 values versus a generic value that may have thousands of values across multiple domains. How useful is an index on Key Value really going to be?

Usually the reasoning behind doing what you are doing is because the domains need to be "user definable". If that is the case then even I am not going to push you towards creating tables on the fly (although that is a feasible approach).

However, if your reasoning is because you think it will be easier to manage than multiple tables, or because you are envisioning a maintenance user interface that is generic for all domains, then stop and think really hard before you continue.

like image 143
Darrel Miller Avatar answered Oct 03 '22 03:10

Darrel Miller