问题描述
我想为学生,老师,班级关系建模.每个学生都与一位老师相关联(老师可以有很多学生).只有三节课.我认为这是三个表:
I want to model a student, teacher, class relationship. Every student is associated with one teacher (the teacher can have many students). There are only three classes. The way I think of this is that there are three tables:
学生表->(学生ID,学生名,班级ID)
Student Table -> (student_id, student_name, class_id)
教师表->(学生ID,学生姓名,班级ID)
Teacher Table -> (student_id, student_name, class_id)
类表->(class_id,class_name)
Class Table -> (class_id, class_name)
我不确定如何在表格中显示学生与老师的关系.我们怎么知道哪个老师被分配给哪个学生?
I'm not sure how to show the student-teacher relationship within the tables. How would we know which teacher is assigned to which student?
推荐答案
这可以通过一些简单的连接来实现.
This can be accomplished with some simple joins.
假设您要查找与某个老师关联的所有学生,则首先抓取teacher
的行.然后,您将加入老师教的classes
.最后,您将加入这些类中的students
.
Assuming that you want to find all the students associated with a certain teacher, you would start off by grabbing the row for the teacher
. You would then join in the classes
that the teacher teaches. Finally, you would join in the students
that are in those classes.
这被称为多对多关系,是数据库中的重要概念.
This is known as a many-to-many relationship, and is an important concept in databases.
select
t.student_name, -- I suspect this col might actually be named teacher_name
s.student_name,
from
-- Find the classes that a teacher teaches
teacher_table t join class_table c on (t.class_id=c.class_id)
-- Find the students in those classes
join student_table s on (s.class_id=c.class_id)
where
t.student_id = ? -- Again, I suspect this should be "teacher_id"
这篇关于如何设计一个简单的数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!