Пары "ключ/значение" в таблице базы данных

Мне нужно создать таблицу Key/value в моей базе данных, и я ищу руководство для наилучшего способа сделать это. В принципе, мне нужно уметь сопоставлять значения с динамическим набором именованных свойств и применять их к внешнему ключу.

Операции, которые мне нужно поддерживать:

Применить пару ключ/значение к группе элементов
Перечислить все активные клавиши
Определите все элементы, которые имеют значение для заданного ключа
Определить все элементы, в которых значение, связанное с заданным ключом, соответствует некоторым критериям.

Кажется, что самый простой способ сделать это - определить таблицу:

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

Похоже, что я, вероятно, дублирую много данных в столбце Key, потому что я могу указать какой-либо данный ключ для большого количества документов. Замена ключа varchar на целочисленный поиск в другой таблице, по-видимому, облегчает эту проблему (и делает ее значительно более эффективной для перечисления всех активных ключей), но вызывает у меня проблему сохранения этой таблицы поиска (восходящей в нее всякий раз, когда я хочу для определения свойства и потенциального удаления записи в любое время, когда ключ/значение очищается).

Какой лучший способ сделать это?

Ответ 1

Используется модель базы данных Entity-Attribute-Value. Это обычный способ хранения пар ключ/значение в реляционной базе данных, но он имеет ряд недостатков в отношении нормализации и эффективности базы данных.

Да, дизайн таблицы, который вы показали, является наиболее распространенным способом сделать это. В этом дизайне каждый атрибут каждого объекта получает отдельную строку в таблице KeyValue.

Применить пару ключ/значение к группе элементов: Вам нужно добавить одну строку для каждого элемента в группе.

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');

Вы также можете подготовить инструкцию INSERT с параметрами и пропустить несколько элементов в цикле или что угодно.

Перечислить все активные клавиши:

SELECT DISTINCT Key FROM KeyValue;

Определить все элементы, которые имеют значение для заданного ключа:

SELECT id FROM KeyValue WHERE Key = 'color';

Определить все элементы, в которых значение, связанное с данным ключом, соответствует некоторым критериям:

SELECT id FROM KeyValue WHERE Value = 'green';

Некоторые из проблем с Entity-Attribute-Value:

Невозможно убедиться, что ключи написаны одинаково для всех элементов.
Невозможно сделать некоторые ключи обязательными для всех элементов (т.е. NOT NULL в обычной табличной конструкции).
Все ключи должны использовать VARCHAR для значения; не может хранить разные типы данных для каждой клавиши.
Невозможно использовать ссылочную целостность; не может сделать FOREIGN KEY, который применяется к значениям некоторых ключей, а не другим.

В принципе, Entity-Attribute-Value не является нормализованным дизайном базы данных.

Ответ 2

Не оптимизируйте это, если вам не нужно. Какова средняя длина ключа? Будет ли эта таблица такой большой, что она не будет вписываться в вашу память на сервере, если вы ее наивно наберете? Я бы предложил реализовать его самым простым способом, измерить производительность и затем повторно реализовать, только если производительность является проблемой.

Если производительность является проблемой, то использование целочисленного ключа и отдельной таблицы, вероятно, является способом перехода (JOINS на целочисленных столбцах обычно быстрее, чем JOINS с использованием столбцов с переменной длиной строки). Но первое правило оптимизации - MEASURE FIRST - убедитесь, что ваш предположительно оптимизированный код действительно ускоряет работу.

Ответ 3

Возможно, стоит попробовать переварить ключ, используя SHA1 или MD5, прежде чем вставлять его в таблицу.

Это позволит вам избавиться от таблицы поиска, но вы не сможете перебирать ключи, потому что это происходит только в одном направлении.

Ответ 4

Создать обновляемые виды!. Также проверьте это для примера.

Ответ 5

Мне кажется, что у вас может быть пара вариантов дизайна.

Выбор 1: дизайн двух таблиц, на который вы указали в своем ответе

Keys (
 id int not null auto_increment
 key string/int
)
values (
 id int not null auto_increment
 key_id int
 value string/varchar/int
)

Выбор 2: возможно, как указал sambo99, вы можете изменить это:

keys (
 id int not null auto_increment
 key string/int
 hash_code int -- this would be computed by the inserting code, so that lookups would effectively have the id, and you can look them up directly
)

values (
 id int not null auto_increment -- this column might be nice since your hash_codes might colide, and this will make deletes/updates easier
 key_id int -- this column becomes optional
 hash_code int
 value string/varchar/int...
)