sql - 数据库表中的键/值对

Question

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

我需要能够支持的操作是:

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

看来最简单的方法是定义一个表:

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

看来我可能会在“键”列中复制大量数据，因为任何给定的键都可能是为大量文档定义的。用对另一个表的整数查找替换 Key varchar 似乎可以缓解这个问题(并且使枚举所有事件键的效率显着提高)，但让我遇到了维护该查找表的问题(每当我想要时就将其插入到其中)定义一个属性，并可能在清除键/值时删除该条目)。

最好的方法是什么？

Answer 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 语句，并在循环中运行多个项目 ID，或者其他方式。

枚举所有当前事件的键:

SELECT DISTINCT Key FROM KeyValue;

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

SELECT id FROM KeyValue WHERE Key = 'color';

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

SELECT id FROM KeyValue WHERE Value = 'green';

实体属性值的一些问题是:

• 无法确保所有项目的键拼写相同
• 无法使某些键对所有项目都是强制的(即传统表设计中的 NOT NULL)。
• 所有键必须使用 VARCHAR 作为值；无法为每个键存储不同的数据类型。
• 无法使用引用完整性；无法创建适用于某些键的值而不适用于其他键的值的 FOREIGN KEY。

基本上，Entity-Attribute-Value 不是标准化的数据库设计。