database 实体关系图。IS A 关系如何转化为表格？

Entity Relationship Diagram. How does the IS A relationship translate into tables?

提问by Brad Thiessen

I was simply wondering, how an ISA relationship in an ER diagram would translate into tables in a database.

我只是想知道，ER 图中的 ISA 关系如何转换为数据库中的表。

Would there be 3 tables? One for person, one for student, and one for Teacher?

有3张桌子吗？一份给人，一份给学生，一份给老师？

Or would there be 2 tables? One for student, and one for teacher, with each entity having the attributes of person + their own?

还是会有2张桌子？一个给学生，一个给老师，每个实体都有人+自己的属性？

Or would there be one table with all 4 attributes and some of the squares in the table being null depending on whether it was a student or teacher in the row?

或者会不会有一个表具有所有 4 个属性，并且表中的某些方块为空，具体取决于该行中是学生还是教师？

NOTE: I forgot to add this, but there is full coverage for the ISA relationship, so a person must be either a studen or a teacher.

注意：我忘了添加这一点，但是 ISA 关系是完全覆盖的，因此一个人必须是学生或教师。

采纳答案by Marco Bonzanini

Assuming the relationship is mandatory (as you said, a person hasto be a student or a teacher) and disjoint (a person is either a student or a teacher, but not both), the best solution is with 2 tables, one for students and one for teachers.

假设关系是强制性的（如你所说，一个人已经是一个学生或教师）和不相交（一个人或者是一个学生或教师，但不能同时），最好的解决办法是用2个表，一个学生和一个给老师的。

If the participation is instead optional (which is not your case, but let's put it for completeness), then the 3 tables option is the way to go, with a Person(PersonID, Name) table and then the two other tables which will reference the Person table, e.g. Student(PersonID, GPA), with PersonID being PK and FK referencing Person(PersonID).

如果参与是可选的（这不是你的情况，但为了完整性，让我们把它放在一边），那么 3 个表选项是要走的路，一个 Person(PersonID, Name) 表，然后是另外两个将引用的表Person 表，例如 Student(PersonID, GPA)，PersonID 是 PK，FK 引用 Person(PersonID)。

The 1 table option is probably not the best way here, and it will produce several records with null values (if a person is a student, the teacher-only attributes will be null and vice-versa).

1 table 选项在这里可能不是最好的方法，它会生成多个值为空的记录（如果一个人是学生，则仅限教师的属性将为空，反之亦然）。

If the disjointness is different, then it's a different story.

如果不相交是不同的，那么这是一个不同的故事。

回答by isharailanga

there are 4 options you can use to map this into an ER,

您可以使用 4 个选项将其映射到 ER，

option 1

选项1

• Person(SIN,Name)
• Student(SIN,GPA)
• Teacher(SIN,Salary)

• 人（罪名，姓名）
• 学生（SIN，GPA）
• 老师（SIN，薪水）

option 2Since this is a covering relationship, option 2 is not a good match.

选项 2由于这是一个覆盖关系，选项 2 不是一个很好的匹配。

• Student(SIN,Name,GPA)
• Teacher(SIN,Name,Salary)

• 学生（SIN，姓名，GPA）
• 老师（SIN，姓名，薪水）

option 3

选项 3

• Person(SIN,Name,GPA,Salary,Person_Type) person type can be student/teacher

• Person( SIN,Name,GPA,Salary,Person_Type) 人员类型可以是学生/教师

option 4

选项 4

• Person(SIN,Name,GPA,Salary,Student,Teacher) Student and Teacher are bool type fields, it can be yes or no,a good option for overlapping

• Person( SIN,Name,GPA,Salary,Student,Teacher) Student 和 Teacher 是 bool 类型字段，可以是 yes 或 no，重叠的好选择

Since the sub classes don't have much attributes, option 3 and option 4 are better to map this into an ER

由于子类没有太多属性，选项3和选项4最好将其映射到ER

回答by Manan Mehta

This answer could have been a comment but I am putting it up here for the visibility.

这个答案可能是一个评论，但我把它放在这里是为了提高知名度。

I would like to address a few things that the chosen answer failed to address - and maybe elaborate a little on the consequences of the "two table" design.

我想解决一些所选答案未能解决的问题 - 并可能详细说明“两张桌子”设计的后果。

The design of your database depends on the scope of your application and the type of relations and queries you want to perform. For example, if you have two types of users (student and teacher) and you have a lot of general relations that all users can part take, regardless of their type, then the two table design may end up with a lot of "duplicated" relations (like users can subscribe to different newsletters, instead of having one M2M relationship table between "users" and newsletters, you'll need two separate tables to represent that relation). This issue worsens if you have three different types of users instead of two, or if you have an extra layer of IsA in your hierarchy (part-time vs full-time students).

数据库的设计取决于应用程序的范围以及要执行的关系和查询的类型。例如，如果你有两种类型的用户（学生和老师），并且你有很多所有用户都可以参与的一般关系，不管他们是什么类型，那么这两种表的设计最终可能会出现很多“重复”关系（就像用户可以订阅不同的时事通讯，而不是“用户”和时事通讯之间有一个 M2M 关系表，您需要两个单独的表来表示该关系）。如果您拥有三种不同类型的用户而不是两种用户，或者您的层次结构中有额外的 IsA 层（兼职学生与全职学生），则此问题会变得更糟。

Another issue to consider - the types of constraints you want to implement. If your users have emails and you want to maintain a user-wide unique constraint on emails, then the implementation is trickier for a two-table design - you'll need to add an extra tablefor every unique constraint.

要考虑的另一个问题 - 您要实施的约束类型。如果您的用户有电子邮件，并且您希望对电子邮件保持用户范围的唯一约束，那么实现两表设计就比较棘手 - 您需要为每个唯一约束添加一个额外的表。

Another issue to consider is just duplications, generally. If you want to add a new common field to users, you'll need to do it multiple times. If you have unique constraints on that common field, you'll need a new table for that unique constraint too.

另一个要考虑的问题通常只是重复。如果要向用户添加新的公共字段，则需要多次执行此操作。如果您对该公共字段有唯一约束，那么您也需要为该唯一约束创建一个新表。

All of this is not to say that the two table design isn't the right solution. Depending on the type of relations, queries and features you are building, you may want to pick one design over the other, like is the case for most design decisions.

所有这些并不是说两个表的设计不是正确的解决方案。根据您正在构建的关系、查询和功能的类型，您可能希望选择一种设计而不是另一种设计，就像大多数设计决策的情况一样。

回答by Naqamel

It depends entirely on the nature of the relationships.

这完全取决于关系的性质。

IF the relationship between a Person and a Student is 1 to N (one to many), then the correct way would be to create a foreign key relationship, where the Student has a foreign key back to the Person's ID Primary Key Column. Same thing for the Person to Teacher relationship.

如果一个人和一个学生之间的关系是 1 到 N（一对多），那么正确的方法是创建一个外键关系，其中学生有一个外键回到这个人的 ID 主键列。人与教师的关系也是如此。

However, if the relationship is M to N (many to many), then you would want to create a separate table containing those relationships.

但是，如果关系是 M 到 N（多对多），那么您需要创建一个包含这些关系的单独表。

Assuming your ERD uses 1 to N relationships, your table structure ought to look something like this:

假设您的 ERD 使用 1 到 N 关系，您的表结构应该如下所示：

CREATE TABLE Person ( sin bigint, name text, PRIMARY KEY (sin) );

CREATE TABLE Person ( sin bigint, name text, PRIMARY KEY (sin) );

CREATE TABLE Student ( GPA float, fk_sin bigint, FOREIGN KEY (fk_sin) REFERENCES Person(sin) );

CREATE TABLE Student (GPA float, fk_sin bigint, FOREIGN KEY (fk_sin) REFERENCES Person(sin) );

and follow the same example for the Teacher table. This approach will get you to 3rd Normal Form most of the time.

并按照教师表的相同示例进行操作。大多数情况下，这种方法将使您达到第三范式。