关系数据库中的键值对

有人有将密钥值对存储在数据库中的经验吗？

我一直在使用这种类型的表:

CREATE TABLE key_value_pairs ( 
    itemid           varchar(32) NOT NULL,
    itemkey         varchar(32) NOT NULL,
    itemvalue       varchar(32) NOT NULL,
    CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey)
)

然后例如可以存在以下行:

 itemid            itemkey        itemvalue    
 ----------------  -------------  ------------ 
 123               Colour         Red            
 123               Size           Medium             
 123               Fabric         Cotton

这种方案的问题是提取数据所需的SQL语法非常复杂.仅创建一系列键/值列会更好吗？

CREATE TABLE key_value_pairs ( 
    itemid            varchar(32) NOT NULL,
    itemkey1        varchar(32) NOT NULL,
    itemvalue1      varchar(32) NOT NULL,
    itemkey2        varchar(32) NOT NULL,
    itemvalue2      varchar(32) NOT NULL,
 . . .etc . . .
)

这将更容易,更快速地查询,但缺乏第一种方法的可扩展性.有什么建议？

1> Darrel Mille..：

---

在继续你的方法之前,我会谦卑地建议你退一步考虑是否真的想将这些数据存储在"键 - 值对"表中.我不知道你的应用程序,但我的经验表明,每次我做了你正在做的事情,后来我希望我创建了一个颜色表,一个织物表和一个尺寸表.

考虑引用完整性约束,如果采用键值对方法,数据库无法告诉您何时尝试在大小字段中存储颜色ID

考虑加入具有10个值的表的性能优势与可能在多个域中具有数千个值的通用值.Key Value的索引真的有用吗？

通常,做你正在做的事情背后的原因是因为域需要是"用户可定义的".如果是这种情况,那么即使我不打算推​​动你动态创建表格(尽管这是一种可行的方法).

但是,如果您的推理是因为您认为它比多个表更容易管理,或者因为您正在设想一个对所有域都通用的维护用户界面,那么在继续之前停下来思考一下.

---

A ++,这就是我想说的,但是你表达的要好得多.我在处理的数据库中有几个键/值对表,我每天都后悔.每次他们完成因为"我们现在需要一个解决方案",每一次,我都知道做错了.

---

@Guido对于某些系统,关系模型不是最好的方法.我的答案仅适用于您选择关系模型来存储数据的情况.

2> Peter Marsha..：

---

还有另一种解决方案介于两者之间.您可以使用xml类型列作为键和值.所以你保留itemid字段,然后有一个xml字段,其中包含为某些键值对定义的xml, 然后当你从数据库中提取数据时,你可以用许多不同的方式处理xml.取决于您的使用情况.这是一个可扩展的解决方案.

3> Matthew Wats..：

---

在大多数情况下,你会使用第一种方法,这是因为你没有真正坐下来思考你的模型."好吧,我们还不知道钥匙会是什么".一般来说,这是非常糟糕的设计.它实际上比将键作为列更慢,它们应该是.

我也会质疑为什么你的id是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",允许进行一些类型检查.

4> Mario..：

---

我曾经在数据库中使用键值对来创建电子表格(用于数据输入),其中出纳员将通过现金抽屉工作来总结他的活动.每个k/v对表示用户输入货币金额的命名单元格.这种方法的主要原因是电子表格很容易发生变化.常规添加新产品和服务(因此出现了新的细胞).此外,某些情况下不需要某些细胞,可能会被丢弃.

我写的应用程序是一个应用程序的重写,它确实将出纳员表分成不同的部分,每个部分在不同的表中表示.这里的问题是,随着产品和服务的增加,需要进行架构修改.与所有设计选择一样,与另一个相比,采取某种方向是有利有弊的.我的重新设计肯定表现得更慢,更快地消耗了磁盘空间; 但是,它非常灵活,允许在几分钟内添加新产品和服务.然而,唯一需要注意的问题是磁盘消耗; 我记得没有其他令人头疼的问题.

如前所述,我通常考虑键值对方法的原因是用户 - 这可能是业务所有者 - 想要创建具有用户特定属性集的自己的类型.在这种情况下,我已经做出以下决定.

如果有,或者没有必要来检索这些属性或搜索数据可以推迟到一旦一个数据块被检索到,我建议存储在单个文本字段中的所有属性(使用JSON,YAML,XML等应用).如果强烈需要通过这些属性检索数据,则会变得混乱.

您可以创建单个"属性"表(id,item_id,key,value,data_type,sort_value),其中sort列将实际值转换为字符串可排序表示.(例如:日期:"2010-12-25 12:00:00",号"0000000001"),或者你可以创建数据类型的单独的属性表(例如string_attributes,date_attributes,number_attributes).两种方法的众多优点和缺点:第一种更简单,第二种更快.两者都会让你写出丑陋复杂的查询.

5> Jarod Elliot..：

---

根据经验,我发现某些密钥将被更广泛地使用或更频繁地查询.然后,我们通常会稍微对设计进行非规范化,以在主"项目"表中包含特定字段.

例如.如果每个项目都有一个颜色,您可以将颜色列添加到项目表中.Fabric和Size可以较少使用,并且可以在键值对表中保持独立.您甚至可以将颜色保留在键值对表中,但复制项表中的数据以获得性能优势.

显然,这取决于数据以及您需要键值对的灵活性.它还可能导致您的属性数据无法正确定位.但是,反规范化确实极大地简化了查询并提高了性能.

我通常只会考虑在性能变得和问题时进行去规范化,而不仅仅是简化查询.