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

Relationships in database design: one-to-one, one-to-many and many-to-many

When designing database tables, relationships must be established between data entities to ensure data integrity. Let’s explore three common relationship types: one-to-one, one-to-many, and many-to-many, and how to implement them.

One-on-one relationship

In a one-to-one relationship, each row in one table has a corresponding unique row in the other table. To achieve this, use a foreign key column in the child table to reference the primary key column in the parent table. For example:

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

A foreign key (student_id) in the address table links each address to a specific student.

One-to-many relationship

In a one-to-many relationship, each row in the "one" side (parent table) can have multiple row counterparts in the "many" side (child table). Use foreign key columns in the child table to link back to the primary key columns of the parent table. For example:

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

Each teacher can have multiple courses, but each course only belongs to one teacher. The foreign key (teacher_id) in the curriculum establishes this relationship.

Many-to-many relationship

A many-to-many relationship exists when each row in one table can be related to multiple rows in another table, and vice versa. To achieve this, create a join table to hold the relationship between the two related tables. For example:

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

Student class schedules track which students belong to each class and vice versa.

Query related data

These relationships allow efficient queries. For example:

• Retrieve all students in a specific course: SELECT * FROM student schedule WHERE course ID = X;
• Get all courses registered by a specific student: SELECT * FROM student course schedule WHERE student ID = Y;

The above is the detailed content of How Do I 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!