使用hstore与多列对比的用例

Question

我在决定使用哪种方法时遇到了一些麻烦。

我有几个实体“类型”，我们称它们为A、B和C，它们共享某些属性（大约10-15个）。我创建了一个名为ENTITIES的表，以及每个共同属性的列。

A、B、C还有一些（大多数）唯一的属性（都是布尔值，大约有10到30个）。 我不确定在建模表时应该采用哪种最佳方法：

1. 为每个属性在ENTITIES表中创建一列，这意味着不共享该属性的实体类型将只有一个空值。
2. 针对每个实体类型的唯一属性使用单独的表，这有点难以管理。
3. 使用hstore列，每个实体将在此列中存储其唯一标志。
4. ???

我倾向于使用3，但我想知道是否有更好的解决方案。

- Trasplazio Garzuglio

---

作为第二点的补充，仅因为您正在使用PostgreSQL，我建议使用表继承。相比于拥有三个表和一个通用的“父实体”表所带来的维护开销，采用可轻松执行的模式更为优越。也就是说，如果使用hstore或者更通用的实体-属性值方法，您将失去数据库中内置的列类型机制。 - yieldsfalsehood

我不知道Postgres支持继承，我会研究一下，谢谢。 - Trasplazio Garzuglio

Answer 1

(4) 继承

从数据库设计的角度来看，最干净的风格可能是继承，就像@yieldsfalsehood在他的评论中建议的那样。以下是更多信息、代码和链接的示例：
使用Postgres选择（检索）来自多个模式的所有记录

然而，当前在Postgres中实现的继承有许多限制。其中之一是您不能为所有继承表定义公共外键约束。仔细阅读关于注意事项的最后一章。

(3) hstore，json (pg 9.2+) / jsonb (pg 9.4+)

对于许多不同或变化的属性，特别是由于您甚至可以在列内的属性上拥有功能性索引，hstore是一个很好的选择：

• hstore键的唯一索引或约束
• 查找JSON数组中元素的索引
• jsonb索引在Postgres 9.4中的运用

EAV类型的存储有其优点和缺点。 这篇dba.SE上的问题提供了很好的概述。

(1)一个表，有很多列

这是一种简单、有点蛮力的选择。根据你的描述，你将最终拥有约100列，其中大部分是布尔类型，且大部分时间为NULL。添加一个列entity_id来标记类型。使用很多列强制执行约束有点棘手。我不会费心去添加太多可能不需要的约束。

最多允许使用1600个列。由于大多数列都为NULL，因此适用这个上限。只要把它降到100-200列以下，就不必担心。在Postgres中，NULL存储是非常便宜的（基本上是每列1个二进制位，但实际情况比较复杂。）。这只会增加每行10-20字节的额外空间。与人们可能认为的相反，它们在磁盘上hstore解决方案很可能要小得多。

虽然这样的表在人眼中看起来很庞大，但对于Postgres来说并不是问题。关系型数据库专门从事 brute force（暴力计算）。你可以定义一组视图（每种实体一种）在基础表上，只选择感兴趣的列，并在适用的情况下使用它们。这就像继承的反向方法。但这样你就可以拥有通用的索引和外键等。不错，我可能会这么做。

话虽如此，决定仍由您自己做出。一切都取决于您需求的细节。

- Erwin Brandstetter

---

个人而言，我反对使用继承，因为无法强制实现主键或唯一约束在父类和所有子类之间的唯一性。有关 Pg 特定建模替代方案的详细信息，请参见 http://dba.stackexchange.com/questions/27057/model-with-variable-number-of-properties-of-different-types 和 http://stackoverflow.com/q/21414092/398670。 - Craig Ringer

非常好的答案，谢谢。如果没有你提供的链接中所述的限制，我可能会选择继承。 hstore似乎更符合我的要求，所以我会选择它。 - Trasplazio Garzuglio

Answer 2

在我的工作领域中，我们经常面临快速变化的需求，并且很少有时间来进行适当的模式升级。通过处理大量包含空值的大记录和高度规范化（名称，值）的数据，我一直在思考将所有常见属性放在正确的列中，而将不同/不常见的属性放在“hstore”或jsonb存储桶中可能是一个不错的选择。

- jimbob