如何设计一个简单的数据库

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:

我想塑造一个学生，老师，班级关系。每个学生都有一个老师(老师可以有很多学生)。只有三个班。我认为有三个表格

Student Table -> (student_id, student_name, class_id)

学生表-> (student_id, student_name, class_id)

Teacher Table -> (student_id, student_name, class_id)

b0 (student_id、student_name、class_id)

Class Table -> (class_id, class_name)

类表-> (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?

我不知道如何在表格中显示师生关系。我们怎么知道哪个老师被分配给哪个学生?

2 个解决方案

#1

This can be accomplished with some simple joins.

这可以通过一些简单的连接来实现。

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"

#2

This is a few more tables than you want, but several of the .NET examples that Microsoft has created revolve around a similar relational database.

这比您希望的要多一些表，但是Microsoft创建的几个. net示例都是围绕类似的关系数据库。

Here is a link to that database: https://msdn.microsoft.com/en-us/library/bb399731(v=vs.100).aspx

下面是该数据库的链接:https://msdn.microsoft.com/en-us/library/bb399731(v=vs.100).aspx

In this example, the student and the teacher are both kept in the person table and are related to the course table through two different Joining tables . . student grade and course instructor.

在本例中，学生和老师都保存在person表中，并通过两个不同的连接表与课程表关联。学生成绩和课程指导老师。

And here is the Contoso University Schema with link: https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application

这里是Contoso大学模式的链接:https://docs.microsoft.com/en- us/aspnet/mvc/overview/gettingstarted/getting-starwith- ef- us-usvc/creating-more -complex-data-model-for- asp-net-mvc应用程序

#1