关系数据库中的键值对

Question

有人在数据库中存储键值对有经验吗？

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

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 . . .

)

这将更容易，更快捷地进行查询，但是缺少第一种方法的可扩展性。有什么建议吗？

Answer 1

在继续您的方法之前，我谨建议您退后一步，考虑您是否真的要将这些数据存储在“键值对”表中。我不知道您的应用程序，但是我的经验表明，每次完成您的工作后，我希望以后创建一个颜色表，织物表和尺寸表。考虑一下参照完整性约束，如果您采用键值对方法，那么当您尝试在size字段中存储颜色id时，数据库将无法告诉您考虑一下在具有10个值的表上联接的性能优势，而在多个域中可能有成千上万个值的通用值。关键值索引真的有用吗？通常，做您正在做的事情的原因是因为域需要是“用户可定义的”。如果是这样，那么即使是我也不会敦促您快速创建表（尽管这是可行的方法）。但是，如果您的推理是因为您认为它比多个表更易于管理，或者因为您正在设想一个对所有域通用的维护用户界面，那么请停下来并认真思考，然后再继续。

Answer 2

还有另一个解决方案介于两者之间。您可以使用xml类型列作为键和值。因此，您保留itemid字段，然后有一个xml字段，其中包含为某些键值对定义的xml，<items> <item key="colour" value="red"/><item key="xxx" value="blah"/></items>&nbsp;然后从数据库中提取数据时，可以用多种不同方式处理xml。取决于您的用法。这是一个可扩展的解决方案。

Answer 3

在大多数情况下，您将使用第一种方法，这是因为您还没有真正坐下来思考模型。“好吧，我们还不知道键是什么。” 通常，这是相当差的设计。这比实际将您的键作为列要慢得多。我还要质疑为什么您的ID是varchar。在极少数情况下，您确实必须实现键/值表，第一种解决方案很好，但是，我通常希望将键保存在单独的表中，这样就不会在键/中存储varchars作为键/值表。例如，CREATE TABLE valid_keys (&nbsp;&nbsp; &nbsp; id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NUMBER(10) NOT NULL,&nbsp; &nbsp; description&nbsp; &nbsp;varchar(32) NOT NULL,&nbsp; &nbsp; CONSTRAINT pk_valid_keys PRIMARY KEY(id));CREATE TABLE item_values (&nbsp;&nbsp; &nbsp; item_id NUMBER(10) NOT NULL,&nbsp; &nbsp; key_id&nbsp; NUMBER(10) NOT NULL,&nbsp; &nbsp; item_value VARCHAR2(32) NOT NULL,&nbsp; &nbsp; CONSTRAINT pk_item_values PRIMARY KEY(item_id),&nbsp; &nbsp; CONSTRAINT fk_item_values_iv FOREIGN KEY (key_id) REFERENCES valid_keys (id));然后，您甚至可以发疯并在按键上添加“ TYPE”，从而进行一些类型检查。

Answer 4

我曾经在数据库中使用键值对来创建电子表格（用于数据输入），其中出纳员可以通过操作现金抽屉来总结其活动。每对k / v代表一个命名单元，用户在其中输入了金额。这种方法的主要原因是电子表格很容易更改。定期添加新产品和服务（因此出现了新单元）。此外，在某些情况下不需要某些单元格，并且可以将其丢弃。我编写的应用程序是对应用程序的重写，该应用程序确实将柜员表分为不同的部分，每个部分在不同的表中表示。这里的问题是，随着产品和服务的添加，需要进行模式修改。与所有设计选择一样，与其他设计选择相比，朝着某个方向发展是有利有弊。我的重新设计无疑会降低磁盘速度并更快地消耗磁盘空间。但是，它非常灵活，可以在几分钟内添加新产品和服务。但是，唯一需要注意的问题是磁盘消耗。我没有其他头痛可以回想。如前所述，我通常考虑使用键值对方法的原因是，用户（可能是企业所有者）希望创建自己的具有用户特定属性集的类型。在这种情况下，我得出以下决定。如果不需要通过这些属性来检索数据，或者一旦检索到大量数据后就可以将搜索推迟到应用程序，则我建议将所有属性存储在单个文本字段中（使用JSON，YAML，XML等）。 ）。如果非常需要通过这些属性来检索数据，则会变得混乱。您可以创建一个“属性”表（id，item_id，键，值，data_type，sort_value），在此表中，排序列会将实际值覆盖为可按字符串排序的表示形式。（例如，日期：“ 2010-12-25 12:00:00”，数字：“ 0000000001”），也可以按数据类型（例如string_attributes，date_attributes，number_attributes）创建单独的属性表。在这两种方法的众多利弊之间：第一种方法更简单，第二种方法更快。两者都会使您编写难看的复杂查询。