关系数据库中的键值对 [英] Key value pairs in relational database

问题描述

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

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

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颜色红色
 123尺寸中等
 123 Fabric Cotton 
  

此方案的问题是提取数据所需的SQL语法相当复杂。
最好只是创建一系列的键/值列？

  CREATE TABLE key_value_pairs b 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。。。。
）
  

这将更容易，更快地查询，但缺乏第一种方法的可扩展性。
任何建议？

解决方案

在继续之前，我会谦卑地建议你退一步，真正想将此数据存储在键 - 值对表中。我不知道你的应用程序，但我的经验表明，每次我做了你在做什么，后来我希望我创建了一个颜色表，一个布料表和一个大小表。

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

考虑加入具有10个值的表的性能优势与可能在多个域中具有数千个值的通用值。

通常，做你正在做的事情的推理是因为域需要是用户可定义的。如果是这样的情况，那么即使我不会推动你在飞行中创建表（虽然这是一个可行的方法）。

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

Does someone have experience with storing key-value pairs in a database?

I've been using this type of table:

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

Then for example the following rows could exist:

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

The trouble with this scheme is the SQL syntax required to extract data is quite complex. Would it be better to just create a series of key/value columns?

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

This will be easier and faster to query but lacks the extensibility of the first approach. Any advice?

解决方案

Before you continue on your approach, I would humbly suggest you step back and consider if you really want to store this data in a "Key-Value Pair"table. I don't know your application but my experience has shown that every time I have done what you are doing, later on I wish I had created a color table, a fabric table and a size table.

Think about referential integrity constraints, if you take the key-value pair approach, the database can't tell you when you are trying to store a color id in a size field

Think about the performance benefits of joining on a table with 10 values versus a generic value that may have thousands of values across multiple domains. How useful is an index on Key Value really going to be?

Usually the reasoning behind doing what you are doing is because the domains need to be "user definable". If that is the case then even I am not going to push you towards creating tables on the fly (although that is a feasible approach).

However, if your reasoning is because you think it will be easier to manage than multiple tables, or because you are envisioning a maintenance user interface that is generic for all domains, then stop and think really hard before you continue.

这篇关于关系数据库中的键值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！