postgresql - 组和子组关系的最优关系设计

Question

我有一些入门级的关系数据库设计问题。我正在从事一个项目，我正在从科学期刊文章中捕获信息并将其存储在 Postgres 数据库中。我的主要目标之一是定义一个足够灵活的架构，以涵盖我在大量论文中可能遇到的大多数案例。实际上，文章倾向于报告一组半标准的细节，但一旦你深入细节，肯定会有差异。这些东西是为人类而不是机器编写的。

在大多数情况下，定义模式非常简单，但我坚持的一件事是如何明智地构建一组表格以捕获有关研究的主题组和主题子集的详细信息。

以一个简单的随机对照试验为例 - 您通常有一组人被确定为合格筛选，一组被确定为合格，一组被随机分配到对照组，一组被随机分配到治疗组。在这些组中的每一个中，您都可以以各种特定方式定义子组，但通常按某种间隔(例如 26-32 岁)或类别(例如怀孕/未怀孕)定义。

目前，我已经设置了这样一个 Study 记录可以有很多 Subject 记录，而 Subject 记录可以有很多 Interval_Subgroup 记录和许多 Categorical_Subgroup 记录。

Subject
-----------------------------------------
id | groupType  | measure | value | study
-----------------------------------------
13 |  treatment |  count  |  578  |  17
14 |   control  |  count  |  552  |  17

Interval_Subgroup
---------------------------------------------------------------
id | factor | factorMin | factorMax | measure | value | subject
---------------------------------------------------------------
41 |  age   |     18    |     24    |  count  |  125  |   13   
42 |  age   |     25    |     32    |  count  |  204  |   13   

Categorical_Subgroup
-----------------------------------------------------
id | factor | factorValue | measure | value | subject
-----------------------------------------------------
74 |  sex   |     male    |  count  |  251  |   13   
75 |  sex   |    female   |  count  |  327  |   13   

这似乎可行，但感觉很笨拙，因为我有两个表用于捕获相同类型的信息。它还具有局限性，因为它不允许我捕获任何子组集的组合，例如 18-24 岁的男性。有些研究报告了那种细节，有些则没有，但我希望能够捕捉到论文提供的任何深度的亚组信息。

有什么比我上面描述的更灵活的方式来构造这些表？我正在尝试勾勒出我认为这应该如何工作，现在，我的主题组有很多子组，子组有很多子组定义。将只有一个表捕获有关子组的度量，另一个表用于定义每个子组是什么。我不确定这是否是正确的方向。也许您可能知道一个更简单的解决方案。

感谢您花时间提供帮助 - 非常感谢!

---

编辑:固定 id 在示例表中是唯一的。

Answer 1

根据您的描述，因素听起来像是一个事物，并且每个子组都有一个或多个因素 s。对我来说，这意味着 factor 需要自己的表。因子又可以是 interval 或 categorical 类型，这意味着 single table inheritance可能是有序的。

示例表可能看起来像这样:

subgroups
------------------------------
id | measure | value | subject
------------------------------
41 |  count  |  125  |   13   
42 |  count  |  204  |   13   

factors
id | type        | factor | category | interval_min | interval_max | subgroup
-----------------------------------------------------------------------------
68 | interval    | age    | NULL     | 18           | 24           | 13
69 | categorical | sex    | male     | NULL         | NULL         | 13

在此示例中，子组 41 有两个因素，年龄 18-24 和性别男性。

也可能是 STI 在这里有点矫枉过正，在这种情况下，您可以将 factor 分成两个表，categorical_factors 和 interval_factors，一个子组可以有零个或多个。

据我所知，使用 STI 的复杂性主要取决于您使用的 ORM。 Rails/ActiveRecord 有很好的支持，其他框架各不相同。

希望对您有所帮助!