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

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

По опыту я обнаружил, что определенные ключи будут более широко использоваться или запрашиваться чаще. Затем мы обычно немного денормализуем дизайн, чтобы включить определенное поле обратно в основную таблицу «элементов».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Я также задаюсь вопросом, почему ваш идентификатор - 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)
);

Затем вы можете даже сходить с ума и добавить к клавишам «TYPE», позволяя выполнять некоторую проверку типов.

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

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

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

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

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

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

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

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

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

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

Основные моменты:

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

Однажды я использовал пары ключ-значение в базе данных с целью создания электронной таблицы (используемой для ввода данных), в которой кассир суммировал бы свою деятельность, работая с денежным ящиком. Каждая пара 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). Среди многочисленных плюсов и минусов обоих подходов: первый проще, второй быстрее. И то, и другое заставит вас писать уродливые и сложные запросы.

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

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

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