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

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

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

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

解决方案

第一种方法还可以。我们可以创建一个UDF来提取所需的数据，然后调用它。

根据经验，我发现某些键将被更广泛地使用或者查询。通常，我们通常会对设计进行稍微归一化，以在主"项目"表中包含特定字段。

例如。如果每个项目都有颜色，则可以将"颜色"列添加到项目表中。 Fabric和Size可能较少使用，并且可以在键值对表中保持分开。我们甚至可以将颜色保留在键值对表中，但可以复制项目表中的数据以获得性能上的好处。

显然，这取决于数据以及键值对的灵活性。这也可能导致属性数据不一致。但是，反规范化确实可以极大地简化查询并提高其性能。

我通常只会考虑在性能出现问题时进行非规范化，而不仅仅是为了简化查询。

第二张表被严重地归一化了。我会坚持第一种方法。

如果可能的键很少，那么我将它们存储为列。但是，如果一组可能的键很大，那么第一种方法就很好(第二种方法将是不可能的)。

还是每个项目只能具有有限数量的键，但是这些键可能来自较大的集合？

我们也可以考虑使用对象关系映射器来简化查询。

第一种方法要灵活得多，但要付出我们提到的代价。

正如我们所展示的，第二种方法永远都不可行。相反，我们会这样做(按照第一个示例)

create table item_config (item_id int, colour varchar, size varchar, fabric varchar)

当然，这仅在数据量已知且变化不大时才起作用。

作为一般规则，任何需要更改表的DDL以进行正常工作的应用程序都应考虑第二和第三点。

我认为，只要给定类型的项的键/值经常更改，我们就在做正确的事。
如果它们是静态的，则仅使项目表更宽更有意义。

我们使用类似(但更复杂)的方法，在键/值周围有很多逻辑，还有每个键允许的值类型的表格。
这使我们可以将项目定义为键的另一个实例，并且我们的中央表将任意键类型映射到其他任意键类型。它可以使大脑迅速陷入困境，但是一旦我们编写并封装了处理所有问题的逻辑，便拥有了很大的灵活性。

如果需要，我可以写出我们要做的更多详细信息。

我不明白为什么对于第一个设计而言，提取数据的SQL应该很复杂。当然要获取项目的所有值，只需执行以下操作：

SELECT itemkey,itemvalue FROM key_value_pairs WHERE itemid='123';

或者，如果我们只想为该商品指定一个特定的钥匙，请执行以下操作：

SELECT itemvalue FROM key_value_pairs WHERE itemid='123' AND itemkey='Fabric';

第一种设计还使我们能够灵活地随时随地轻松添加新密钥。

如果键是动态的，或者有很多键，则使用我们拥有的映射表作为第一个示例。此外，这是最通用的解决方案，它在将来添加更多密钥时可以最佳扩展，很容易对SQL进行编码以获取数据，并且数据库将能够比我们想象的更好地优化查询(也就是说，除非证明它是以后测试的瓶颈，否则我不会过早地优化这种情况，在这种情况下，我们可以考虑下面的两个选择。

如果键是一个已知集合，并且键数目不多(<10，也许是<5)，那么我认为将它们作为项的值列不会出现问题。

如果已知的固定键数量适中(10 30)，则可能需要另一个表来保存item_details。

但是，我从来没有见过需要使用第二个示例结构，它看起来很麻烦。

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

只要仍然可以满足业务要求，就可以违反规范化规则。可以确定拥有" key_1，value_1，key_2，value_2，... key_n，value_n"，直到需要" key_n + 1，value_n + 1"为止。

我的解决方案是为共享属性提供数据表，为唯一属性提供XML表。这意味着我要同时使用两者。如果所有内容(或者大多数内容)都具有大小，则大小是表中的一列。如果只有对象A具有属性Z，则类似于Peter Marshall给出的答案，将Z存储为XML。

在大多数情况下，我们将使用第一种方法，这是因为我们还没有真正坐下来思考模型。通常，这是相当差的设计。这比实际将键作为列要慢得多。

我还要问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(id),
    CONSTRAINT fk_item_values_iv FOREIGN KEY (key_id) REFERENCES valid_keys (id)
);

然后，我们甚至可以发疯，并在按键上添加" TYPE"，从而可以进行一些类型检查。

在继续方法之前，我谨建议我们退后一步，考虑我们是否真的要将这些数据存储在"键值对"表中。我不知道应用程序，但是我的经验表明，每次完成工作后，我希望以后创建一个颜色表，一个织物表和一个尺寸表。

考虑一下参照完整性约束，如果我们采用键值对方法，那么当我们尝试在size字段中存储颜色id时，数据库将无法告诉我们

考虑一下在具有10个值的表上联接的性能优势，而在多个域中可能有成千上万个值的通用值。关键值索引真正有用的是什么？

通常，做我们正在做的事情的原因是因为域必须是"用户可定义的"。如果是这样，那么即使是我也不会敦促我们快速创建表(尽管这是可行的方法)。

但是，如果推理是因为我们认为它比多个表更易于管理，或者因为我们正在设想一个对所有域通用的维护用户界面，那么请停下来并认真思考，然后再继续。

如果我们走KVP表的路线，并且我不得不说我自己完全不喜欢该技术，因为它确实很难查询，那么我们应该考虑使用适当的技术将单个项目ID的值聚在一起无论我们使用哪种平台

RDBMS倾向于分散行，以避免插入时发生块争用，如果要检索8行，则很容易发现自己正在访问表的8个块以读取它们。在Oracle上，最好考虑使用散列集群来存储这些散列，这将大大提高访问给定项id的值时的性能。

示例不是使用键值对的很好的示例。更好的示例是在计费应用程序中使用诸如费用表，客户表和客户_费用表之类的东西。费用表将包含以下字段：
费用编号，费用名称，费用说明
Customer_Fee表将包含以下字段：
customer_id，fee_id，fee_value

我认为设计此类表格的最佳方法如下：

• 将常用字段设置为数据库中的列。
• 提供一个Misc列，其中包含一个字典(采用JSON / XML /其他字符串格式)，该字典将这些字段作为键值对。

重点：

• 在大多数情况下，我们可以编写普通的SQL查询来查询SQL。
• 我们可以对键值对执行FullTextSearch。 MySQL有一个全文本搜索引擎，否则我们可以使用"点赞"查询，但查询速度稍慢一些。虽然全文搜索不好，但我们假设此类查询较少，因此不会引起太多问题。
• 如果键值对是简单的布尔值标志，则此技术的功能与为键创建单独的列具有相同的功能。对键值对的任何更复杂的操作都应在数据库外部进行。
• 查看一段时间内查询的频率，可以告诉我们哪些键值对需要按列进行转换。
• 此技术还使对数据库强制完整性约束变得容易。
• 它为开发人员提供了重构其架构和代码的更自然的途径。

我曾经在数据库中使用键值对来创建电子表格(用于数据输入)，在该电子表格中，柜员将从工作现金抽屉中总结出他的活动。每对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)创建单独的属性表。在这两种方法的众多利弊之间：第一种方法更简单，第二种方法更快。两者都会导致我们编写难看的复杂查询。