How to design a simple database

Question

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)

Teacher Table -> (student_id, student_name, class_id)

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?

Answer 1

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.

Here is a link to that database: 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.

And here is the Contoso University Schema with link: https://learn.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

Answer 2

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"