StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to link multiple tables together into a single query
text
Body
copied!<p>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 <code>$_POST['children_name']</code>, 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. </p> <p>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? </p> <p>(NOTE THAT THERE ARE 3 TABLES THERE)</p> <p><a href="http://s22.postimg.org/mnwzpog5d/bazadate.jpg" rel="nofollow noreferrer"> Image </a></p> <p>LATER EDIT: Fluffeh`s solution, a little bit edited! " I would consider the following structure for your tables.</p> <p>First, a table for all the schools that the website will have data from.</p> <pre><code>id schoolname 1 European College#1 2 European Colelge#2 </code></pre> <p>Then, a table of all the classes that are active in a college.</p> <pre><code>id Class 1 8A 2 8B 3 8C </code></pre> <p>then a table for the subjects offered by all the schools. You will need an ID and then any other information as needed.</p> <pre><code> schoolSubject id name 1 mathematics 2 geography 3 physics 4 geometry </code></pre> <p>Next up a table containing relations between the schools, classes and school subjects.</p> <pre><code>relations SchoolID ClassID SubjectID 1 1 1 1 1 2 1 1 4 </code></pre> <p>(so, if my logic is correct, class 8A students from the "European College#1" will study mathematics, geography and geometry)</p> <p>Lastly, we will need to know which student is from where. </p> <pre><code>students id name class_id schoolname_id 1 John Doe 1 1 2 Jane Doe 2 2 </code></pre> <p>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.</p> <pre><code>grades subjectID studentID grade 1 1 B,A,B 1 2 A,B,B 2 1 A,C,C 2 2 B,A,A </code></pre> <p>With this structure in place, what query could I use?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload