sql - Key/Value pairs in a database table

Question

I need to design a Key/value table in my database and I'm looking for guidance on the best way to do this. Basically, I need to be able to associate values to a dynamic set of named properties and apply them to an external key.

The operations I need to be able to support are:

• Apply a key/value pair to a group of items
• Enumerate all of the currently-active keys
• Determine all of the items that have a value for a given key
• Determine all of the items where the value associated with a given key matches some criteria.

It seems that the simplest way to do this is to define a table:

CREATE TABLE KeyValue (
  id    int,
  Key   varchar...,
  Value varchar...
);

It seems that I am likely to be duplicating a lot of data in the Key column because I any given key is likely to be defined for a large number of documents. Replacing the Key varchar with an integer lookup into another table seems to alleviate this problem (and make it significantly more efficient to enumerate all of the active keys), but sticks me with the problem of maintaining that lookup table (upserting into it whenever I want to define a property and potentially removing the entry any time a key/value is cleared).

What's the best way to do this?

Answer 1

You are employing a database model called Entity-Attribute-Value. This is a common way to store key/value pairs in a relational database, but it has a number of weaknesses with respect to database normalization and efficiency.

Yes, the table design you showed is the most common way to do it. In this design, every attribute of every entity gets a distinct row in your KeyValue table.

Apply a key/value pair to a group of items: You need to add one row for each item in the group.

INSERT INTO KeyValue (id, key, value) VALUES (101, 'color', 'green');
INSERT INTO KeyValue (id, key, value) VALUES (102, 'color', 'green');
INSERT INTO KeyValue (id, key, value) VALUES (103, 'color', 'green');

You may also prepare the INSERT statement with parameters and run through a number of item id's in a loop, or whatever.

Enumerate all of the currently-active keys:

SELECT DISTINCT Key FROM KeyValue;

Determine all of the items that have a value for a given key:

SELECT id FROM KeyValue WHERE Key = 'color';

Determine all of the items where the value associated with a given key matches some criteria:

SELECT id FROM KeyValue WHERE Value = 'green';

Some of the problems with Entity-Attribute-Value are:

• No way to make sure keys are spelled the same for all items
• No way to make some keys mandatory for all items (i.e. NOT NULL in a conventional table design).
• All keys must use VARCHAR for the value; can't store different data types per key.
• No way to use referential integrity; can't make a FOREIGN KEY that applies to values of some keys and not others.

Basically, Entity-Attribute-Value is not a normalized database design.