Postgres外键关联多张表

Question

我有一个概念问题，至今我还没有找到一个令人满意的解决方案。我将用一个例子来解释这个问题。

我有两张表，分别是“cats”和“dogs”，它们由不同的属性组成（由于我的实际情况更加复杂，所以需要这些不同的表）。我可以给这些“动物”喂食，并且我想要跟踪一些关于这次喂养操作的信息（例如“[animal_fed]”，“food_type”，“food_quantity”，“date”等）。

目前我正在使用一个名为“feeds”的表，其架构如下：animal_id INTEGER，“table_name VARCHAR(50)”（可能是“cats”或“dogs”，但会有很多其他物种...），[other fields]。

我完全不满意这个解决方案，因为它使得从“feeds”中检索出被喂养动物的相关信息变得非常麻烦（如果可能的话，但必须有一种方法...），类似于“join”。

如何更好地解决这个问题？

使用“父”表是否是一个好的解决方案？

或者，如果您认为我的实际方法很好，如何进行“join”以获取所有“feeds”信息以及动物的名称？

谢谢。

- mettjus

---

不，你不能有一个外键指向猫或狗表中的任何一个。标准的解决方法是将所有动物放在同一张表中，并添加一个字段来描述动物种类。 - joop

@joop，问题是我的示例很简单。实际实体彼此之间的性质相当不同，因此我无法将它们全部放在同一张表中。更好的例子是planes和pens，它们都可以进行maintenance，我需要跟踪maintenance.date。我知道我不能创建到多个表的“适当”fk，但我需要一个解决方法。顺便说一句，谢谢！ - mettjus

这也是一个简化的答案。 - joop

Answer 1

有几种方法可以改进它，但是你的解决方案已经非常不错了：

1. 将所有动物放在一个表中。您说它们需要不同，但也许您可以合并它们。最终的animals表将具有来自两个表的所有列。您将拥有animal_type列，该列将是dog或cat。
2. 多个SQL查询。您可以将一切保持原样，并创建一些方法/函数来获取所需的数据。进行一次查询以获取所有饲料，然后在另一个查询中，您可以获取这些动物。如果要限制查询，可以添加条件FROM animals WHERE id IN ( ANIMAL_ID_FROM_PREVIOUS_RESULT )。然后您必须在应用程序中合并这些内容。
3. 您可以创建两个关系表。您将拥有animals表和feeds表，并创建r_dog_feed和r_cat_feed。它们将具有引用feeds表的feed_id，然后是dog_id或cat_id。然后，您可以使用UNION编写SQL：

查询狗的名字和食物信息，使用r_dog_feed表联接dogs和feeds表；查询猫的名字和食物信息，使用r_cat_feed表联接cats和feeds表。

- Ladislav Gallay

---

感谢您的回复。目前看来，方法2似乎是最可行的，因为：

• 方法1：正如我回答@joop的那样，实体的性质非常不同，这种方法会导致创建一个具有超过70％空字段的表。
• 方法3：动物->饲料更像是“1对N”的关系，而这种方法会导致“N对N”的关系（但我当然也可以尝试这种方法）。

- mettjus

第二种关系被称为多态关系，基本上就是你开始的那样：你有_id和_type列，并且通过_type告诉它指向哪个表。这里有一个更好的例子https://dev59.com/7k7Sa4cB1Zd3GeqP0Q0q，使用**UNION**。我认为它会满足你的需求。 - Ladislav Gallay

这个不错。至少现在我能够检索到所有需要的数据。谢谢。 - mettjus

Answer 2

如果你无法将猫和狗的数据放在同一张表中，那么另一个好的解决方案是拥有一个动物表，保存猫和狗的共同数据。然后，你可以在动物和猫之间、动物和狗之间建立一对零或一的关系。

在动物表上，添加一个animal_type字段，告诉你在哪个其他表中可以找到特定的额外信息。

在猫和狗上，添加一个指向动物表的animal_id外键。

在饲料表上，有一个指向动物表的animal_id和外键。

优点：

你可以轻松处理饲料和动物之间的关系。只需要访问常见的动物信息的SQL很容易编写。

一些应用框架支持“继承”的数据子类型，并为你处理大部分内容。但由于你没有提及你的应用程序使用什么，我不确定这是否适用。

缺点：

你无法仅通过数据库约束来强制执行完整性。你需要触发器或应用程序逻辑来确保没有猫或狗引用相同的animal_id，并且每个动物都是猫或狗（如果这是你的要求之一）。

当您需要访问猫和狗的额外信息时，您可能需要单独使用SQL语句，因为结构不同且访问数据必须使用不同的表。

如果您需要添加其他动物类型，您将添加表格...并添加不同的SQL以访问额外信息...这不太美观，可能会失控。

- Frazz

---

谢谢。这也是一个有用的答案。实际上，无法通过数据库约束来强制完整性是一个问题。我自己也在考虑（正如您从我的问题中可以看到的那样）为我的实体使用一种父表。顺便说一下，这可能是我在中期内采取的方法。 - mettjus