基于Postgresql继承的数据库设计

Question

我正在开发一个简单的保姆应用程序，有两种类型的用户：‘父母’和‘保姆’。我使用postgresql作为我的数据库，但我在设计我的数据库时遇到了问题。

“父母”和“保姆”实体具有可以泛化的属性，例如:用户名、密码、电子邮件……这些属性可以放入名为“User”的父实体中。他们也都有自己的属性，例如：->年龄的保姆。

在OOP方面，事情对我来说非常清楚，只要扩展user类就可以了，但是在DB设计中事情就不同了。在发布这个问题之前，我在互联网上闲逛了一周，希望能深入了解这个“问题”。我确实找到了很多信息但是

在我看来，似乎有很多不同意见。以下是我读过的一些帖子：

How do you effectively model inheritance in a database?：TPT-Per-Type，Table-Per-Hierarchy (TPH)和Table-Per-Concrete (TPC) VS“强迫RDb进入基于类的需求是不正确的”。

https://dba.stackexchange.com/questions/75792/multiple-user-types-db-design-advice：

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

https://dba.stackexchange.com/questions/36573/how-to-model-inheritance-of-two-tables-mysql/36577#36577

When to use inherited tables in PostgreSQL?：postgresql中的继承对于我和其他一群用户来说并不像预期的那样工作，正如最初的帖子所指出的那样。

我真的很困惑我应该采取哪种方法。在我的OOP思维中，类-表-继承(https://stackoverflow.com/tags/class-table-inheritance/info)似乎是最正确的，但我非常感谢并更新了DB的观点。

Answer 1

我对数据库世界中继承的看法是“只能是一种。”没有其他关系建模技术适用于这种特定情况；即使使用check约束，使用严格的关系模型，您也会遇到将错误的“一类”人员放到错误的表中的问题。因此，在您的示例中，用户可以是父母或保姆，但不能同时是两者。如果一个用户可以是多个类型的用户，那么继承就不是最好的工具。

教师/学生关系只有在学生不能成为教师的情况下才能很好地发挥作用，反之亦然。例如，如果您有一个TA，最好使用严格的关系设计进行建模。

因此，回到家长保姆的问题，您的桌子设计可能如下所示：

CREATE TABLE user (
  id SERIAL,
  full_name TEXT,
  email TEXT,
  phone_number TEXT
);

CREATE TABLE parent (
  preferred_payment_method TEXT,
  alternate_contact_info TEXT,
  PRIMARY KEY(id)
) INHERITS(user);

CREATE TABLE babysitter (
  age INT,
  min_child_age INT,
  preferred_payment_method TEXT,
  PRIMARY KEY(id)
) INHERITS(user);

CREATE TABLE parent_babysitter (
  parent_id INT REFERENCES parent(id),
  babysitter_id INT REFERENCES babysitter(id),
  PRIMARY KEY(parent_id, babysitter_id)
);

这种模式允许用户“只有一种”用户--父母或临时保姆。请注意主键定义是如何留给子表的。在此模型中，您可以在父母和保姆之间拥有重复的ID，但这可能不是问题，这取决于您如何编写代码。(注意: Postgres是我所知道的唯一具有此限制的ORDBMS -例如，Informix和Oracle在继承表上继承了键)

还可以看到我们如何在中混合关系模型-我们在父母和保姆之间有一个多对多的关系。这样我们就可以将实体分开，但我们仍然可以在没有奇怪的自引用键的情况下对关系进行建模。

Answer 2

所有选项都可以大致表示为以下情况：

1. 基表+每个类的表(类-表继承、每个类型的表、来自dba.stackexchange)
2. single表继承的建议(每个层次结构的表)-只需将所有内容放入单个表中
3. 为每个类创建独立的表(Table-Per-Concrete)

我通常更喜欢选项(1)，因为(2)和(3)在DB设计方面并不完全正确。

使用(2)，某些行将有未使用的列(例如，对于Parent，"age“将为空)。对于(3)，你可能有重复的数据。

但您还需要考虑数据访问方面的问题。使用选项(1)时，数据将分布在几个表中，因此要获得Parent，需要使用join操作从User和Parent表中选择数据。

我认为这就是选项(2)和(3)存在的原因--它们在SQL查询方面更容易使用(不需要连接，您只需从一个表中选择所需的数据)。