Пара ключевых значений в реляционной базе данных

Ответ 1

Прежде чем продолжить свой подход, я смиренно предлагаю вам отступить и подумать, хотите ли вы хранить эти данные в таблице "Key-Value Pair". Я не знаю вашего приложения, но мой опыт показал, что каждый раз, когда я делал то, что вы делаете, позже, я бы хотел, чтобы я создал таблицу цветов, таблицу ткани и таблицу размеров.

Подумайте о ограничениях ссылочной целостности, если вы примете подход пары "ключ-значение", база данных не сможет сказать вам, когда вы пытаетесь сохранить идентификатор цвета в поле размера

Подумайте о преимуществах производительности соединения в таблице с 10 значениями по сравнению с общим значением, которое может иметь тысячи значений для нескольких доменов. Насколько полезен индекс по ключевому значению, который действительно будет?

Обычно рассуждения о том, что вы делаете, - это то, что домены должны быть "определяемыми пользователем". Если это так, то даже я не собираюсь подталкивать вас к созданию таблиц "на лету" (хотя это приемлемый подход).

Однако, если ваши аргументы состоят в том, что вы считаете, что управлять ими будет проще, чем несколько таблиц, или потому, что вы предполагаете, что пользовательский интерфейс обслуживания, который является общим для всех доменов, прекратите и подумайте очень тяжело, прежде чем продолжить.

Ответ 2

Существует другое решение, которое находится где-то между ними. Вы можете использовать столбец типа xml для ключей и значений. Таким образом, вы сохраняете поле itemid, а затем имеете поле xml, которое содержит xml, определенный для некоторых пар значений ключа, таких как <items> <item key="colour" value="red"/><item key="xxx" value="blah"/></items> Затем, когда вы извлекаете данные из базы данных, вы можете обрабатывать xml несколькими способами. В зависимости от вашего использования. Это расширенное решение.

Ответ 3

В большинстве случаев вы бы использовали первый метод, потому что вы на самом деле не сели и не продумали свою модель. "Ну, мы еще не знаем, какие ключи будут". Вообще, это довольно плохой дизайн. Это будет медленнее, чем фактическое использование ключей в виде столбцов, какими они и должны быть.

Я также спрашиваю, почему ваш идентификатор varchar.

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

например,

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(item_id),
    CONSTRAINT fk_item_values_iv FOREIGN KEY (key_id) REFERENCES valid_keys (id)
);

Затем вы даже можете сходить с ума и добавить "ТИП" к клавишам, что позволит провести некоторую проверку типов.

Ответ 4

Я когда-то использовал пары ключ-значение в базе данных с целью создания электронной таблицы (используемой для ввода данных), в которой кассир суммировал свою деятельность с работой денежного ящика. Каждая пара k/v представляла собой именованную ячейку, в которую пользователь вводил денежную сумму. Основная причина такого подхода заключается в том, что электронная таблица сильно подвержена изменениям. Новые продукты и услуги были добавлены регулярно (появились новые ячейки). Кроме того, определенные ячейки не нужны в определенных ситуациях и могут быть отброшены.

Приложение, которое я написал, было переписано приложением, которое разломило кассовый лист на отдельные разделы, представленные в другой таблице. Проблема заключалась в том, что при добавлении продуктов и услуг были необходимы модификации схемы. Как и во всех вариантах дизайна, есть плюсы и минусы в отношении определенного направления по сравнению с другим. Мой редизайн, конечно, выполнял медленнее и быстрее потреблял дисковое пространство; однако он был очень проворным и позволял добавлять новые продукты и услуги за считанные минуты. Единственным примечанием, однако, было потребление диска; я не мог вспомнить других головных болей.

Как уже упоминалось, причиной, по которой я обычно рассматриваю подход пары "ключ-значение", является то, что пользователи - это может быть владельцем бизнеса - хотят создавать свои собственные типы, имеющие набор атрибутов, специфичный для пользователя. В таких ситуациях я пришел к следующему определению.

Если нет необходимости извлекать данные по этим атрибутам, или поиск может быть отложен на приложение после извлечения фрагмента данных, я рекомендую хранить все атрибуты в одном текстовом поле (используя JSON, YAML, XML, и т.д.). Если есть настоятельная потребность в извлечении данных по этим атрибутам, она становится беспорядочной.

Вы можете создать единую таблицу атрибутов (id, item_id, key, value, data_type, sort_value), где столбец сортировки покрывает фактическое значение в сортируемое строкой представление. (например, дата: "2010-12-25 12:00:00", номер: "0000000001" ). Или вы можете создавать отдельные таблицы атрибутов по типу данных (например, string_attributes, date_attributes, number_attributes). Среди многочисленных плюсов и минусов обоих подходов: первое проще, второе - быстрее. Оба заставят вас писать уродливые сложные запросы.

Ответ 5

Из опыта я обнаружил, что некоторые ключи будут более широко использоваться или запрашиваться чаще. Обычно мы слегка де-нормировали дизайн, чтобы включить конкретное поле в основную таблицу "item".

например. если каждый элемент имеет цвет, вы можете добавить столбец "Цвет" в таблицу товаров. Ткань и размер могут использоваться реже и могут храниться отдельно в таблице пар ключ-значение. Вы даже можете сохранить цвет в таблице пар ключ-значение, но дублируйте данные в таблице элементов, чтобы получить преимущества по производительности.

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

Я бы обычно рассматривал де-нормализацию, когда производительность становится и проблема, а не просто для упрощения запроса.

Ответ 6

Я не понимаю, почему SQL для извлечения данных должен быть сложным для вашего первого дизайна. Конечно, чтобы получить все значения для элемента, вы просто делаете это:

SELECT itemkey,itemvalue FROM key_value_pairs WHERE itemid='123';

или если вам нужен только один конкретный ключ для этого элемента:

SELECT itemvalue FROM key_value_pairs WHERE itemid='123' AND itemkey='Fabric';

Первый дизайн также дает вам возможность легко добавлять новые ключи, когда захотите.

Ответ 7

Я думаю, что лучший способ для проектирования таких таблиц выглядит следующим образом:

• Сделать часто используемые поля в виде столбцов в базе данных.
• Предоставить столбец Misc, который содержит словарь (в JSON/XML/other string formeat), который будет содержать поля в виде пар ключ-значение.

Точки выделения:

• Вы можете написать обычные SQL-запросы для запроса SQL в большинстве ситуаций.
• Вы можете использовать FullTextSearch для пар ключ-значение. MySQL имеет полнотекстовый поисковый движок, иначе вы можете использовать "похожие" запросы, которые немного медленнее. Хотя полнотекстовый поиск плох, мы предполагаем, что таких запросов меньше, поэтому это не должно вызывать слишком много проблем.
• Если ваши пары ключ-значение являются простыми булевыми флагами, этот метод имеет ту же силу, что и отдельный столбец для ключа. Любая более сложная операция над парами значений ключей должна выполняться вне базы данных.
• Глядя на частоту запросов в течение определенного периода времени, вы сообщите, какие пары ключ-значение должны быть преобразованы в столбцы.
• Этот метод также упрощает принудительное ограничение целостности базы данных.
• Это обеспечивает более естественный путь для разработчиков, чтобы повторно закрепить их схему и код.

Ответ 8

первый метод вполне нормально. вы можете создать UDF, который извлекает нужные данные и просто вызывает это.

Ответ 9

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

Или это так, что каждый элемент может иметь только конечное число ключей, но ключи могут быть чем-то из большого набора?

Вы также можете рассмотреть возможность использования объектного реляционного сопоставления для упрощения запросов.

Ответ 10

Первый метод намного более гибкий по той цене, которую вы упомянули.

И второй подход никогда не будет жизнеспособным, как вы показали. Вместо этого вы бы сделали (согласно вашему первому примеру)

create table item_config (item_id int, colour varchar, size varchar, fabric varchar)

Конечно, это будет работать только тогда, когда количество данных будет известно и не сильно изменится.

Как правило, любому приложению, требующему изменения DDL таблиц для нормальной работы, следует дать вторую и третью мысли.

Ответ 11

Нарушение правил нормализации прекрасное, пока деловое требование все еще может быть выполнено. Наличие key_1, value_1, key_2, value_2, ... key_n, value_n может быть ОК, вплоть до нужной вам точки key_n+1, value_n+1.

Мое решение было таблицей данных для общих атрибутов и XML для уникальных атрибутов. Это означает, что я использую оба. Если все (или большинство вещей) имеют размер, то размер - это столбец в таблице. Если только объект A имеет атрибут Z, то Z сохраняется как XML, аналогичный ответ Питера Маршалла, уже заданный.

Ответ 12

PostgreSQL 8.4 поддерживает тип данных hstore для хранения наборов пар (ключ, значение) в одном поле данных PostgreSQL. Пожалуйста, обратитесь к http://www.postgresql.org/docs/8.4/static/hstore.html за информацией об использовании. Хотя это очень старый вопрос, но он думал об этом, думая, что это может помочь кому-то.

Ответ 13

Вторая таблица плохо деформирована. Я бы придерживался первого подхода.

Ответ 14

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

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

Я могу написать более подробную информацию о том, что мы делаем, если потребуется.

Ответ 15

Если клавиши динамические или их множество, используйте таблицу сопоставления, которая у вас есть в качестве первого примера. Кроме того, это самое общее решение, оно масштабируется лучше всего в будущем, поскольку вы добавляете больше ключей, легко закодировать SQL, чтобы получить данные, и база данных сможет оптимизировать запрос лучше, чем вы могли себе представить ( т.е. я бы не стал прилагать усилия к преждевременной оптимизации этого случая, если это не было доказано, что это будет узким местом при тестировании позже, и в этом случае вы могли бы рассмотреть следующие два варианта ниже).

Если ключи являются известным набором, и их не так много (< 10, может быть, < 5), то я не вижу проблемы с тем, что они являются столбцами значений для элемента.

Если есть среднее число известных фиксированных ключей (10-30), возможно, у вас есть другая таблица для хранения item_details.

Однако я никогда не вижу необходимости использовать вашу вторую примерную структуру, она выглядит громоздкой.

Ответ 16

Если вы переходите по маршруту таблицы KVP, и я должен сказать, что мне вообще не нравится эта техника, так как ее действительно сложно запросить, тогда вы должны рассмотреть возможность кластеризации значений для одного элемента id вместе, используя подходящий метод для любой платформы, на которой вы находитесь.

RDBMS имеют тенденцию рассеивать ряды вокруг, чтобы избежать конфликтов блоков в вставках, и если у вас есть 8 строк для извлечения, вы можете легко найти доступ к 8 блокам таблицы для их чтения. В Oracle вам следует рассмотреть хэш-кластер для их хранения, что значительно повысит производительность при доступе к значениям для данного элемента.

Ответ 17

Времена изменились. Теперь у вас есть другие типы баз данных, которые вы можете использовать рядом с реляционными базами данных. Варианты NOSQL теперь включают в себя: Столбцы, Хранилища документов, График и Multi-модель (см. http://en.wikipedia.org/wiki/NoSQL).

Для баз данных Key-Value ваш выбор включает (но не ограничивается) CouchDb, Redis и MongoDB.

Ответ 18

Ваш пример - не очень хороший пример использования пар ключей. Лучшим примером может быть использование чего-то вроде таблицы Плата таблицы Customer и таблицы Customer_Fee в приложении для выставления счетов. Таблица Плата будет состоять из таких полей, как:   fee_id, fee_name, fee_description Таблица Customer_Fee будет состоять из таких полей, как:   customer_id, fee_id, fee_value