How to Implement One-to-One, One-to-Many, and Many-to-Many Relationships in Database Design?

How to implement one-to-one, one-to-many and many-to-many relationships in database design

When designing database tables, it is crucial to understand how to implement various types of relationships between entities. This article delves into the concepts and techniques for implementing one-to-one, one-to-many, and many-to-many relationships.

One-on-one relationship

In a one-to-one relationship, a row in one table is uniquely related to a row in another table. To implement this relationship, create a foreign key column in the table representing the "many" side that references the primary key column of the table representing the "one" side. Additionally, unique constraints are enforced on foreign key columns to prevent duplicate relationships.

<code>学生表：学生ID，姓名，地址ID
地址表：地址ID，地址，城市，邮政编码，学生ID</code>

One-to-many relationship

In a one-to-many relationship, one row in one table can be related to multiple rows in another table. To do this, include a foreign key column in the table representing the "many" side that references the primary key column of the table representing the "one" side.

<code>教师表：教师ID，姓名
课程表：课程ID，课程名称，教师ID</code>

Many-to-many relationship

In a many-to-many relationship, multiple rows in one table can be related to multiple rows in another table. To achieve this, create a join table with two foreign key columns, one referencing each primary key of the related table.

<code>学生表：学生ID，姓名
课程表：课程ID，名称，教师ID
学生课程表：课程ID，学生ID</code>

Example query

To efficiently retrieve related data, perform joins between tables based on foreign key relationships:

<code>-- 获取特定课程中的学生：

SELECT s.student_id, 姓名
FROM 学生课程表 sc
INNER JOIN 学生表 s ON s.student_id = sc.student_id
WHERE sc.class_id = X

-- 获取特定学生的课程：

SELECT c.class_id, 名称
FROM 学生课程表 sc
INNER JOIN 课程表 c ON c.class_id = sc.class_id
WHERE sc.student_id = Y</code>

The above is the detailed content of How to Implement One-to-One, One-to-Many, and Many-to-Many Relationships in Database Design?. For more information, please follow other related articles on the PHP Chinese website!