创建两个连接的表的数据库选择? [英] Database choice for creating two connected tables?
问题描述
我有数据库表课程和学生。
- 课程表有列(_id ,course_number)。
- 学生表有列(student_name,student_number,course_number)。
我已创建课程表,但我在创建学生表时遇到问题。
两个课程可能有相同的学生,因此同一个学生行将存在两次,在学生表中有不同的course_number
。我不想要复制行。这样我不能把student_number
作为主键
。因为它将为多个类存在两次。如何设计这个表,我不能把多个course_number
放在同一列。
这是一个多对多的经典案例,为此,您需要在课程和学生之间建立第三个表格。该模式将如下所示:
课程
表有列(course_id,course_name)
Student_course
表有列(student_id,course_id);
学生
表格为列(student_id,student_name)
Student_course表
示例数据:
课程:
id | name
------------------
1 |数学
2 |英语
3 |科学
学生
code> id | name
---------------
1 | Tom
2 | Dick
3 | Harry
Student_course
code> student_id | course_id
------------------------
1 | 1
1 | 2
2 | 1
3 | 3
在此示例中,学生1(Tom)在课程1和2(数学,英语) ,
学生2(迪克)仅在课程1(数学)
只在课程3(科学)
I have to database tables "Courses" and "Students".
- Courses table has columns ("_id", "course_name", "course_number").
- Student table has columns ("student_name", "student_number", "course_number").
I've created Courses table, but i have problem with creating Students table.
Two classes may have same student, so same student row will exist twice with different "course_number"
s in Students table. I don't want replicate rows. In this way i can't also make "student_number"
as primary key
. Because it will exist twice for multiple classes. How to design this tables, i can't put multiple "course_number"
s to same columns.
This is a classic case of many-to-many, and for that, you'll need a third table between Course and Student. The schema will look something like this:
Course
table has columns ("course_id", "course_name")
Student_course
table has columns ("student_id", "course_id");
Student
table as columns ("student_id", "student_name")
Student_course table has foreign key constraints on both student and course tables.
Example data:
Course:
id | name
------------------
1 | Maths
2 | English
3 | Science
Student
id | name
---------------
1 | Tom
2 | Dick
3 | Harry
Student_course
student_id | course_id
------------------------
1 | 1
1 | 2
2 | 1
3 | 3
In this example, Student 1 (Tom) is on courses 1 and 2 (Maths, English),
Student 2 (Dick) is on course 1 only (Maths)
Student 3 (Harry) is on course 3 only (Science)
这篇关于创建两个连接的表的数据库选择?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!