基于PostgreSQL继承的数据库设计

Question

我正在开发一个简单的保姆应用程序，有两种类型的用户：'家长'和'保姆'。我正在使用postgresql作为我的数据库，但我在解决数据库设计方面遇到了麻烦。

'家长'和'保姆'实体具有可以概括的属性，例如：用户名、密码、电子邮件等。这些属性可以放入名为'用户'的父实体中。它们也都有自己的属性，例如：保姆->年龄。

就面向对象编程而言，对我来说非常清楚，只需扩展用户类即可。但在数据库设计方面，情况是不同的。 在发布这个问题之前，我在互联网上漫游了一个星期，寻找关于这个“问题”的见解。我确实找到了很多信息，但似乎存在很多分歧。以下是我阅读过的一些帖子： 如何有效地在数据库中建模继承？：表-每类型（TPT）、表-每层次（TPH）和表-每混凝土（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

何时在PostgreSQL中使用继承表？：如原帖所指出，PostgreSQL中的继承并不像我和其他用户预期的那样有效。

我真的很困惑应该采取哪种方法。类表继承(https://stackoverflow.com/tags/class-table-inheritance/info)似乎是我面向对象思维中最正确的方法，但我非常希望得到更新的数据库思维意见。

- Jdruwe

Answer 1

在数据库世界中，我认为继承的方式是“只能是一种类型”。在这种特定情况下，没有其他关系建模技术适用；即使使用检查约束和严格的关系模型，也存在将错误的“类型”人员放入错误表中的问题。因此，在您的示例中，用户可以是父母或保姆，但不能同时拥有这两种身份。如果用户可以是多种类型的用户，则继承不是最好的工具。

在教师/学生关系中，只有在学生不能成为教师或反之亦然的情况下才能很好地发挥作用。例如，如果有一个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在继承表上具有继承键)。同时，我们还混合了关系模型——我们在父母和保姆之间建立了多对多的关系。这样我们就能保持实体分离，同时又能够建立一个关系，而不需要使用奇怪的自引用键。

- Brad

---

非常好的回答！谢谢！ - Alex Luis Arias

Answer 2

所有选项大致可以用以下案例表示：

1. 基础表+每个类别的表（类-表继承，表-每类型，dba.stackexchange的建议）
2. 单表继承（表-每层次结构） - 将所有内容放入单个表中
3. 为每个类创建独立的表（表-每个具体内容）

我通常更喜欢选项（1），因为（2）和（3）在数据库设计方面不完全正确。

使用（2）时，某些行将有未使用的列（例如，“年龄”对于“Parent”为空）。而使用（3）可能会有重复的数据。

但是您还需要考虑数据访问。使用选项（1），数据分布在几个表中，因此要获取“Parent”，您需要使用“join”操作从“User”和“Parent”表中选择数据。

我认为这就是选项（2）和（3）存在的原因 - 从SQL查询的角度来看它们更易于使用（不需要连接，您只需从一个表中选择所需的数据）。

- Borys Serebrov