代码之家 › 专栏 › 技术社区 › Nathan Ridley

您将如何构造实体模型来存储具有不同数据类型的任意键/值数据?

database-design

Nathan Ridley · 技术社区 · 14 年前

我经常遇到这样的场景:使用每行键/值模型而不是刚性的列/字段模型将一组任意数据存储在表中会很有用。问题是,我希望用正确的数据类型存储值,而不是将所有内容转换为字符串。这意味着我必须选择一个具有多个可空列的表,每个数据类型一个,或者一组值表,每个数据类型一个。我也不确定是否应该使用完整的第三范式,将键分离到一个单独的表中,通过一个外键从值表中引用它们,或者是否最好保持简单,将字符串键存储在值表中,并接受字符串的重复。

旧/坏:

此解决方案使添加附加值在流体环境中非常困难,因为需要定期修改表。

MyTable
============================
ID    Key1    Key2    Key3
int   int     string  date
----------------------------
1     Value1  Value2  Value3
2     Value4  Value5  Value6

单表解决方案

此解决方案允许通过单个表简化。查询代码仍然需要检查空值以确定字段存储的数据类型。可能还需要检查约束来确保只有一个值字段包含非空数据。

DataValues
=============================================================
ID    RecordID    Key    IntValue    StringValue    DateValue
int   int         string int         string         date
-------------------------------------------------------------
1     1           Key1   Value1      NULL           NULL
2     1           Key2   NULL        Value2         NULL
3     1           Key3   NULL        NULL           Value3
4     2           Key1   Value4      NULL           NULL
5     2           Key2   NULL        Value5         NULL
6     2           Key3   NULL        NULL           Value6

多表解决方案

此解决方案允许对每个表进行更简洁的用途,尽管代码需要提前知道数据类型,因为它需要为每个数据类型查询不同的表。索引可能更简单、更有效,因为需要索引的列更少。

IntegerValues
===============================
ID    RecordID    Key    Value
int   int         string int
-------------------------------
1     1           Key1   Value1
2     2           Key1   Value4

StringValues
===============================
ID    RecordID    Key    Value
int   int         string string
-------------------------------
1     1           Key2   Value2
2     2           Key2   Value5

DateValues
===============================
ID    RecordID    Key    Value
int   int         string date
-------------------------------
1     1           Key3   Value3
2     2           Key3   Value6

你如何处理这个问题?哪个解决方案更好?

此外,是否应将键列分隔成单独的表并通过外键引用,或者应将其保留在值表中,并在由于某种原因键名发生更改时进行大容量更新?

4 回复 | 直到 14 年前

Thomas 14 年前

首先,关系数据库不是为存储任意数据而设计的。关系模型的基础在于获取将要存储的数据的性质的规范。

其次,您建议的是实体属性值(EAV)的变体。EAV的问题在于数据完整性、报告、性能和维护。它们有它们的位置,但它们类似于药物:在有限的数量和狭小的环境中使用它们会有益;过量会杀死你。

针对EAV编写查询是一种负担。因此,如果您要使用EAV,我看到它们成功的唯一情况是限制它们的使用,这样就不允许任何人编写筛选特定属性的查询。也就是说,不允许任何人编写类似于 Where AttributeName = 'Foo' . 这意味着您不能过滤、排序、计算或将特定属性放在报表的特定位置。EAV数据只是一袋分类的数据,可以在报告中大量地吐出来,但就是这样。我甚至见过有人将EAV作为XML块来实现。

现在,如果您在这里使用EAV,并且因为它只是一个数据块,所以我将使用单表方法。单表方法的一个显著好处是,您可以添加一个检查约束,以确保在IntValue、StringValue或DateValue列中只有一个值。空值不会花很多钱,如果这只是一堆数据,它不会对性能产生任何影响。此外,它还可以简化查询,因为您可以使用简单的case语句返回字符串、整数或日期值。

我可以看到多表方法的许多问题,其中最不重要的是,没有什么可以阻止同一属性具有多种类型的值(例如,integervalues中的一行和stringvalues中的一行)。此外,要获取数据,您必须始终使用三个左联接,这将使您的查询编写起来更麻烦。

电动汽车的成本是纪律和警惕。它要求开发团队中的规程在任何情况下都不要针对特定属性编写报告或查询。开发人员将从管理层得到很大的压力,“只是这一次”写一些特定属性的过滤器。一旦你永远走在黑暗的道路上,它就会支配你的开发和维护。EAV必须保持一堆数据,而不是更多。如果您不能在开发团队中保持这种纪律,那么我就不会实现EAV。为了避免以后的维护噩梦,我需要为任何新列提供规范。一旦用户确实希望对报表进行筛选、排序、计算或将属性放在特殊位置,则该属性必须成为第一类列。如果您能够维护它们的使用规则,那么EAV可以很好地让用户存储他们想要的任何信息,并推迟您需要获取数据元素规范的时间,直到用户希望以前面提到的方式使用该属性。

flow 14 年前

我更喜欢将键和值放在一张表中。 我现在正在建立的数据库以简单的主语/谓词/宾语短语收集关于汉字的数据点;主语和谓词都是字符串,但对象可以有任何类型。表中有一些额外的结构信息(如谓词类型),但不多。

我的数据库结构的一个特殊特征是谓词实际上被分割成几个部分键。要了解这是如何有用的,让_s考虑字符_:的一些数据点:

äºº / reading / chinese / rÃ©n
äºº / reading / japanese / on / jin
äºº / reading / japanese / on / nin
äºº / reading / japanese / kun / hito
äºº / used-in / taiwan
äºº / reading / prc
äºº / reading / japan
äºº / meaning / chinese / english / man; person; human
äºº / meaning / japanese / english / man; person; human; quantifier for people
äºº / form / strokecount / 2
äºº / form / strokeorder / 34

每行代表一个数据点。第一个元素是主语,最后一个是宾语,中间是谓词部分。谓词部分有一个固定的列数(3到5列很可能足够“平坦”比嵌套更好);未使用的部分接收空值。使用此模式,可以很容易地构造SQL语句,返回有关给定字符的所有事实,或有关若干给定字符的所有日语读数(on和kun),或具有至少13个和最多24个笔画的所有字符,等等:

subject  predicate1  predicate2  predicate3 ob_type  ob_int ob_text       ob_bool
äºº       reading     chinese                text            rÃ©n
äºº       reading     japanese    on         text            jin
äºº       reading     japanese    on         text            nin
äºº       reading     japanese    kun        text            hito
äºº       used-in     taiwan                 bool                          true
äºº       reading     prc                    bool                          true
äºº       reading     japan                  bool                          true
äºº       meaning     chinese     english    text            man; perso...
äºº       meaning     japanese    english    text            man; perso...
äºº       form        strokecount            int       2
äºº       form        strokeorder            int       34

这种方法的好处在于,如果没有太多的思考和预先计划,您很快就可以开始向表中输入数据。当新的事实出现时,它们大多数时候都会适合这个非常一般的结构;当你发现某些谓词很笨拙时,收集违规记录并更新它们以携带你最喜欢的新措辞并不太困难。不再迁移架构。哎呀!

更具体地说,为了回答您的问题,我考虑了很多关于是否将值放在单独的表中,以及是否在另一个表中实际表示谓词的问题。

这是完全可能的,但对于我最初的版本,我发现了它 更重要的是保持简单 ;如果在某种程度上,存储所有这些repetitve字符串会损害存储和性能(我的意思是,我的数据库中有大约70000个字符的strokeCounts,因此仅此顺序是 ( len( 'form' ) + len( 'strokecount' ) ) * 7e4 == 1e6 字节只是为了说明谓词),我相信迁移到更复杂的方法相对容易。唉,这也意味着你必须修改你的查询。

当我听到有人声称当然你必须把那些重复的谓词和不同的值类型放在不同的表中时,我只是礼貌地微笑。 数据库已经优化了几十年,以应付大量的数据和有效地组织稀疏表,该死。 我的意思是,整个方法都违背了每个人都告诉你如何去做的,所以为什么不大胆一点呢?

最后,我认为有三个主要因素可以帮助您决定如何构建数据库:

1)您能想出合理的SQL语句吗? 给你期望的答案?(如果不是这样,您还必须决定是否遇到了SQL的一个固有限制,一个可能或可能无法用不同的DB模式解决的限制)。

2)这些查询是否表现良好? 我从经验中知道,用另一种方式(在一些MB的sqlite-db中)可以在性能上产生巨大的差异,因此即使您选择了一种大表方法并获得不满意的查询时间,也可能是模式出了错,但选择另一种查询相同数据的方法也可能会导致你的速度提高了10倍。

3)可扩展性。可扩展性。可扩展性。 这是一个困难的问题,但也许你知道你所要做的就是收集关于你个人大理石收藏的数据。在这种情况下,很难做出错误的决定。如果你承诺在不到一秒钟的时间内将你国家出版的任何一本书上的数据传送到世界上的每一台桌面,那么很难做出正确的事情。大多数现实场景都介于两者之间,所以可伸缩性意味着要问:如果这个或那个工具最终成为性能瓶颈,我是否能够升级它,或者失败地迁移到另一个产品?我说,一张大桌子的方法非常简单,替代方案应该是丰富的。

啊,也许你会感兴趣:我正在调查 redis 这是一个不时髦的数据库。它看起来很有趣,而且很容易使用。“一个大表”方法应该与那些非常流行的couchdb/mongodb/whathaweyou_document-oriented,schema-free__数据库非常兼容。

Zackman 14 年前

我肯定会使用多表方法,这将使您的查询更简单,并消除许多未使用的字段。如果您希望规范化您的数据库,您还应该将键分离到一个额外的表中,但这只是一个优先事项。

我能想到的唯一更好的方法是使用面向文档的数据库,比如 MongoDB .

Evan Plaice 14 年前

将值存储为原始二进制

通过将数据转换为原始字节[]格式,将值存储在varbinary字段中。

为什么把所有的东西都转换成弦很糟糕:

将所有内容转换为字符串非常昂贵,因为它们实际上是一种转换。

例如:

ints、double、float、bools、dates等…所有这些都需要转换成ASCII字符,这在存储和处理方面都是昂贵的(特别是因为它们需要转换回)。

相信我,我以前做过这样的工作,把所有的东西都转换成双精度(我只使用标量值),它起作用了,但很糟糕。

如果将所有内容转换为bytes[],则不存在“real”格式转换,因为您正在复制类型的基元版本。唯一的补充是,您还必须存储类型,这样当您将数据从表中拉出来时,您就知道如何将其转换为数据。

您有两种选择:

添加其他类型的列
通过添加包含位标志的字节来指示类型,将类型连接到varbinary的前面

例如:

0000 0001 - int
0000 0010 - float
0000 0011 - double
0000 0100 - bool
0000 0101 - string
0000 0111 - date

选项1添加了另一列,您说过不需要。
选项2增加了复杂性。如果您能处理位和字节,它是最好/最有效的方法。使用Bitmash设置/读取类型标志,并使用array.copy()和convert.to type()方法将它们恢复到正确的类型。

有时,知道如何使用二进制和字节[]是一个巨大的优势。

更新

我不清楚为什么这次投票被否决。它满足了包含简单的每行键值对关系的问题要求。考虑到不需要向表中添加不必要的列,以及以二进制表示形式存储的“本机”格式的值,这也是一种快速而高效的方法。

它增加了从表中插入/提取项的额外步骤,但是普通字典/数组在较低的级别上也会这样做。唯一的主要区别是,这种存储值的方法携带着它的类型。

这种方法唯一真正的缺点是:

无法以有效的方式搜索值(因为需要对其进行强制转换以进行检查)。
它增加了复杂性,因为需要将数据强制转换为二进制格式并强制转换为二进制格式,并且需要存储类型。(与添加类型列和将所有内容强制转换为字符串相比,这仍然是一个显著的改进。