在设计表格时如何实现一对一，一对多和多对多的关系？ [英] How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

问题描述

任何人都可以解释如何实现一对一，一对多和多对多的关系，同时设计一些例子的表格？

解决方案

一对一：使用外键引用表：

  student：student_id，first_name，last_name，address_id 
 address：address_id，address，city，zipcode，student_id＃你可以有一个
＃link back如果您需要
  

一对多：使用外键关系的许多方面链接到一个方面：

  teacher：teacher_id， first_name，last_name＃oneside 
类：class_id，class_name，teacher_id＃manyside 
  

多对多：使用联结表（示例）：

  student：student_id，first_name，last_name 
类：class_id，name，teacher_id 
 student_classes：class_id，student_id＃连接表
  

示例查询：

   - 让所有学生上课：
 
 SELECT s.student_id，last_name 
 FROM student_classes sc 
 INNER JOIN学生s ON s.student_id = sc.student_id 
 WHERE sc.class_id = X 
 
  - 获取学生的所有课程：
 
 SELECT c.class_id，name 
 FROM student_classes sc 
 INNER JOIN classes c ON c.class_id = sc.class_id 
 WHERE sc.student_id = Y 
  / pre> 
 
 
 
 
Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?
解决方案
One-to-one: Use a foreign key to the referenced table:
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
                                                        # "link back" if you need
One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:
teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side
Many-to-many: Use a junction table (example):
student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table
Example queries:
 -- Getting all students for a class:

    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 

    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y


                        
这篇关于在设计表格时如何实现一对一，一对多和多对多的关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！