如何将多个表一起链接到单个查询中

I am in need of help. I never worked with complex databases, and I don't know how to arrange the tables. I need a table with children names, which is linked to their parents account (did this using $_POST['children_name'], etc when submitting the registration form), but now I need to link their schools with their grades (example I am in 8 A grade), then their classes with the individual grade school subjects, and then link marks (like A+ in America) and absences to individual children.

我需要帮助。我从未使用复杂的数据库,我不知道如何安排表格。我需要一个带有子名称的表格,这些表格与他们的父母帐户相关联(在提交注册表格时使用$ _POST ['children_name']等),但现在我需要将他们的学校与他们的成绩联系起来(例如我是在8 A级),然后他们的班级与个别小学的学科,然后链接标记(如美国的A +)和个别孩子的缺席。

I have no idea on how to do this. I made an example, which is illustrated in the photo below. Could you please help me?

我不知道如何做到这一点。我做了一个例子,如下图所示。请你帮助我好吗?

(NOTE THAT THERE ARE 3 TABLES THERE)

(注意那里有3个表格)

Image

LATER EDIT: Fluffeh`s solution, a little bit edited! " I would consider the following structure for your tables.

LATER EDIT:Fluffeh的解决方案,有点编辑! “我会考虑你的桌子的以下结构。

First, a table for all the schools that the website will have data from.

首先,该网站将提供数据的所有学校的表格。

id    schoolname
 1     European College#1
 2     European Colelge#2

Then, a table of all the classes that are active in a college.

然后,列出了大学里活跃的所有课程。

id    Class
 1    8A
 2    8B
 3    8C

then a table for the subjects offered by all the schools. You will need an ID and then any other information as needed.

然后是所有学校提供的科目表。您需要一个ID,然后根据需要提供任何其他信息。

    schoolSubject
id  name
1   mathematics
2   geography
    3       physics
    4       geometry

Next up a table containing relations between the schools, classes and school subjects.

接下来是一张包含学校,班级和学校科目之间关系的表格。

relations
SchoolID   ClassID     SubjectID
1           1               1
1           1               2
1           1               4

(so, if my logic is correct, class 8A students from the "European College#1" will study mathematics, geography and geometry)

(所以,如果我的逻辑是正确的,来自“欧洲大学#1”的8A级学生将学习数学,地理和几何学)

Lastly, we will need to know which student is from where.

最后,我们需要知道哪个学生来自哪里。

students
id  name        class_id   schoolname_id
1   John Doe     1           1
2   Jane Doe     2           2

The last tabe pretty much gives us the ability to have a record of multiple grades for the same student for the same subject - in case a student has to take a subject more than once.

最后一个tabe几乎让我们能够为同一个科目记录同一个学生的多个成绩 - 如果学生不得不多次学习一个科目。

grades
subjectID   studentID   grade
1           1           B,A,B
1           2           A,B,B
2           1           A,C,C
2           2           B,A,A

With this structure in place, what query could I use?

有了这个结构,我可以使用什么查询?

1 个解决方案

#1

I would consider the following structure for your tables.

我会考虑你的表的以下结构。

Firstly, a table for the subjects offered by the school. You will need an ID and then any other information as needed.

首先,为学校提供的科目表。您需要一个ID,然后根据需要提供任何其他信息。

schoolSubject
id  name
1   mathematics
2   geography

Next up a table containing student information. Again, we have a unique ID, then any info as needed

接下来是包含学生信息的表格。同样,我们有一个唯一的ID,然后根据需要提供任何信息

student
id  name
1   John Doe
2   Jane Doe

The next table allows what is known as a many to many relationship. basically it means you can have multiple entries for the same subject (as many students can be enrolled in each) and multiple entries for students (as students can be enrolled in multiple subjects).

下一个表允许所谓的多对多关系。基本上它意味着您可以为同一主题创建多个条目(每个学生可以注册多个条目)和多个学生条目(因为学生可以注册多个科目)。

studentEnrolment
subjectID   studentID
1           1
1           2
2           1

The last tabe pretty much gives us the ability to have a record of multiple grades for the same student for the same subject - in case a student has to take a subject more than once.

最后一个tabe几乎让我们能够为同一个科目记录同一个学生的多个成绩 - 如果学生不得不多次学习一个科目。

grades
subjectID   studentID   grade
1           1           B
1           2           A
2           1           A
2           2           B

With this stucture in place, you can do a query like the following:

有了这个结构,你可以进行如下查询:

select
    subj.name,
    stud.name,
    grad.grade
from
    schoolSubject subj
        join studentEnrolment enr
            on subj.id=enr.subjectID
        join student stud
            on enr.studentID=stud.studentID
        join grades grad
            on subj.subjectID=grad.subjectID
            and stud.id=grad.studentID