数据库表中的键/值对 [英] Key/Value pairs in a database table

问题描述

我需要在我的数据库中设计一个键/值表，我正在寻找有关执行此操作的最佳方法的指导.基本上，我需要能够将值与一组动态命名属性相关联，并将它们应用于外部键.

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:

• 将键/值对应用于一组项目
• 枚举所有当前活动的键
• 确定所有具有给定键值的项目
• 确定与给定键关联的值符合某些条件的所有项目.

似乎最简单的方法是定义一个表:

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

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

看来我很可能会在 Key 列中复制大量数据，因为我可能会为大量文档定义任何给定的键.将 Key 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).

最好的方法是什么?

推荐答案

您正在使用名为 实体-属性-值.这是在关系数据库中存储键/值对的常用方法，但它在数据库规范化和效率方面存在许多弱点.

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.

是的，您展示的表格设计是最常用的方法.在此设计中，每个实体的每个属性在您的 KeyValue 表中都有一个不同的行.

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

您还可以准备带有参数的 INSERT 语句，并在循环中运行多个项目 ID，或其他任何内容.

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

枚举所有当前活动的键:

SELECT DISTINCT Key FROM KeyValue;

确定所有具有给定键值的项目:

SELECT id FROM KeyValue WHERE Key = 'color';

确定与给定键关联的值与某些条件匹配的所有项目:

SELECT id FROM KeyValue WHERE Value = 'green';

Entity-Attribute-Value 的一些问题是:

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

• 无法确保所有项目的键拼写相同
• 无法为所有项目强制设置某些键(即在传统表设计中非空).
• 所有键必须使用 VARCHAR 作为值；不能为每个键存储不同的数据类型.
• 无法使用参照完整性；无法创建适用于某些键的值而不适用于其他键的值的 FOREIGN KEY.

基本上，Entity-Attribute-Value 不是规范化的数据库设计.

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

这篇关于数据库表中的键/值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！