postgresql - hstore 针对多列的用例

Question

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

我有几个实体“类型”，我们称它们为 A、B 和 C，它们共享一定数量的属性(大约 10-15 个)。我创建了一个名为 ENTITIES 的表，并为每个公共(public)属性创建了一列。

A、B、C 也有一些(大部分)独特的属性(都是 bool 值，大约可以是 10 到 30 个)。我不确定在对表进行建模时应遵循的最佳方法是什么:

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

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

Answer 1

(4) Inheritance

从数据库设计的角度来看，最简洁的样式可能是继承，就像@yieldsfalsehood 在他的评论中建议的那样。以下是包含更多信息、代码和链接的示例:
Select (retrieve) all records from multiple schemas using Postgres

不过，目前 Postgres 中继承的实现有一些限制。其中，您不能为所有继承表定义一个公共(public)外键约束。仔细阅读 the last chapter about caveats。

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

对于 很多 不同的或不断变化的属性集来说，这是一个很好的选择，尤其是因为您甚至可以在列内的属性上使用函数索引:

• unique index or constraint on hstore key
• Index for finding an element in a JSON array
• jsonb indexing in Postgres 9.4

EAV 类型的存储有其自身的一系列优点和缺点。 This question on dba.SE 提供了非常好的概述。

(1) 一张表有很多列

这是一种简单的蛮力替代方法。从你的描述来看，你最终会得到大约 100 列，其中大部分是 bool 值，大部分时间都是 NULL 。添加一列 entity_id 来标记类型。对于很多列，按类型强制执行约束有点尴尬。我不会为可能不需要的太多约束而烦恼。

The maximum number of columns allowed is 1600 。由于大多数列为 NULL，因此适用此上限。只要将它保持在 100 - 200 列，我就不会担心。 NULL 存储在 Postgres (basically 1 bit per column, but it's more complex than that.) 中非常便宜。这只是每行多出 10 - 20 个字节。与人们可能假设的 (!) 相反，很可能在磁盘上比 hstore 解决方案小得多。

虽然这样的表在人眼中看起来很可怕，但 Postgres 处理起来没问题。 RDBMSes 擅长暴力破解。您可以在基表的顶部定义一组 View (针对每种类型的实体)，其中仅包含感兴趣的列，并在适用的情况下使用这些列。这就像继承的反向方法。但是这样你就可以拥有公共(public)索引和外键等。还不错。 我可能会那样做。

话虽如此，决定权仍然在您手中。这完全取决于您的要求的详细信息。