数据库表中的键/值对

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

我需要支持的操作是:

将键/值对应用于一组项目

枚举所有当前活动的密钥

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

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

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

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

似乎我可能会在Key列中复制大量数据,因为我可能会为大量文档定义任何给定的键.用一个整数查找替换Key varchar到另一个表似乎缓解了这个问题(并且使枚举所有活动密钥的效率明显提高),但是我仍然坚持维护查找表的问题(随时随地插入它)定义属性,并在键/值被清除时可能删除条目.

最好的方法是什么？

您正在使用名为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';

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

无法确保所有项目的键拼写相同

无法为所有项目强制使用某些键(即传统表格设计中的NOT NULL).

所有键必须使用VARCHAR作为值; 不能为每个密钥存储不同的数据类型.

无法使用参照完整性; 不能使FOREIGN KEY适用于某些键的值而不适用于其他键.

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

除非必须,否则不要对此进行优化.密钥的平均长度是多少？如果你以天真的方式实现它,那么这个表是否会如此之大以至于它们都不适合你服务器的内存？我建议以最简单的方式实现它,测量性能,然后仅在性能出现问题时重新实现.

如果性能是一个问题,那么使用整数键和单独的表可能是要走的路(整数列上的JOINS通常比使用可变长度字符串列的JOINS更快).但优化的第一条规则是MEASURE FIRST--确保您所谓的优化代码实际上确实使事情运行得更快.