映射关系-外键或映射表 [英] Mapping relationship -- foreign key or mapping table

问题描述

尝试在此处获得一些有关创建架构关系时哪个选择更好的专业想法

Try to get some professional idea here about which choice is better when creating schema relationships

我从帖子，但仍尝试获得更多思考。

I roughly find an idea from this post but still try to get more thinkings.

一个简单的情况可能是这样的：

A simple scenario could be like this:

class <--> Student <--> Teacher <--> class

（假设一个老师可以教多门课程）
多对多关系。并且查询可以从任何对等方开始到任何方向。那么什么是更好的设计呢？

(Assume a teacher can teach multiple classes) it is a normal scenario with many-to-many relationship in a round. And query can start from any peer to any direction. So what could be a better design?

我知道在一对一关系中映射表绝对是浪费，但是映射表解决方案是 ONLY 对多对多关系有好处，就像提到的那个帖子？如果我们想扩展与方向的多对多关系。如果关系是单向而不是双向，答案是否会有所不同？

I know in one to one relationship a mapping table is definitely a waste, but is the mapping table solution ONLY good for many-to-many relationship, like that post mentioned? If we want to extend the many to many relationship with directions. If the relationship is one-direction instead of bidirectional, could the answer be different?

预先感谢。

推荐答案

一些简单的规则：

对于一对一关系，可以在关系中涉及的任何一个表中保存外键，并引用另一个表。

For a one-to-one relationship, a foreign key can be held in either of the tables involved in the relationship, referencing the other table.

对于一对多关系，关系的多面表应具有外键。

For a one-to-many relationship, the table on the many side of the relationship should hold the foreign key.

对于许多-to-many关系，您可以创建一个关联表（按您的术语映射表），这是第三个表，其中包含有涉及多对多关系的两个表的外键。

For a many-to-many relationship, you can create an association table (mapping table in your terms), which is a third table that holds foreign keys to both of the tables involved in the many-to-many relationship.

例如，假设我们有表学生， CLASS 和老师。通常，学生和 CLASS 之间存在多对多关系，而上课的老师（假设一堂课只有一名老师）。因此，这些表可能看起来像：

For example, assume we have the tables STUDENT, CLASS, and TEACHER. Generally, there is a many-to-many relationship between STUDENT and CLASS, and a one-to-many from TEACHER to CLASS (assuming a class only has one teacher). So, these tables might look like:

STUDENT: STUDENT_ID, STUDENT_NAME, etc.
CLASS: CLASS_ID, TEACHER_ID (foreign key), CLASS_NAME, etc.
TEACHER: TEACHER_ID, NAME, etc.
STUDENT_CLASS (mapping table): STUDENT_ID (foreign key), CLASS_ID (foreign key), GRADE, etc.

这篇关于映射关系-外键或映射表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！