如何设计一个简单的数据库 [英] How to design a simple database

问题描述

我想为学生，老师，班级关系建模.每个学生都与一位老师相关联(老师可以有很多学生).只有三节课.我认为这是三个表:

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"

这篇关于如何设计一个简单的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！