I’m using laravel on this project.
I have these tables;
Main problem here is the relationship between section and subject.
What would be the right relationship for this?
Subject can have many sections and a Section can have many subjects.
Teacher teaches many subjects and a subject can be taught by many teacher.
But how would I know the section of the subject that the person is teaching? Is it okay if I have a three way many-to-many-relationship?
I would suggest you normalize your DB before you get going. “Teacher” and “Student” are roles of people. People can have more than one role. i.e, A teacher can be a student and vise versa. The current approach is not scalable. You would have to keep adding tables for each role that comes up.
You might say in your case, that teachers will never be students at the same school. Nevertheless the less, I would still say to lay out the DB properly. Person table, Roles Table, and a Joiner/Junction table with person id and role id tying the relationships together.
I agree with @benanamen on the need to normalize/simplify your structure as it will allow for more flexibility, but to answer your question, the reason you’re having confusion is your theory of what a teacher teaches.
In reality, they are not teaching a subject - they are teaching a section. Now, they may be teaching ALL the sections for a subject, but they may not be. You can have different sections being taught by different teachers for the same subject. Heck, you could have multiple teachers for a section, or a teacher and grad assistant or a teacher and a student teacher.
You can infer what subject a teacher is teaching by means of some joins. Same for determining what subjects a student takes.
So high level, I would have something like:
SUBJECT (say Intro to Computer Science)
SECTION (with SubjectID then section number; start date, end date, section meeting day/time)
PERSON (name, id, etc)
ROLE (teacher, student, student teacher, etc)
SECTION_ROLE (sectionID, personID, roleID)
Arguments could be made that section could be broken down further, but personally, I would consider that overkill unless you’re doing very specific reporting.
Is what you’re describing actually a ‘three way’ many-to-many relationship?
Subject can have many sections; can a section have many subjects? What’s the definition of a Section?
It should be Class but it is a reserved keyword in php, so i used Section instead.
But if section has the foreign key then there would be section duplicates.
Thank you for helping me. @benanamen @DaveMaxwell @m_hutley
I’ll work on normalizing the Users table.
Well, again, I’ve never had a class in school that taught more than one subject…
That was a rather important part to leave out. I was under the impression it was subject categorys and sub categorys based on your description, “Subject can have many sections and a Section can have many subjects.”.
It doesnt matter that class is reserved. Your not going to have that word free floating in your script in this use case.
How about giving us the high level overview of what you are doing rather than asking about your attempted solution to doing it.
Here’s what’s in my mind. I’ll emphasize the tables that will be used.
A school contains many Course, for example, Computer Science. Then a course contains many Sections, for example, CS-1, CS-2… etc. Then a section contains many Subjects, for example, Math, English… etc.
I primarily set the relationship of Section and Subject to one-to-many, and the Subject has the section_id. It doesn’t have any problem when I’m assigning a teacher to a subject because the subject has a section_id, so I can just reference it. But I do have a problem when it comes to Student, because that means that they can enroll to the same subject two or more times but with different sections.
But I thought that the logic between Section and Subject is wrong. Clearly, a subject for example, Math can be taught to multiple sections, and a Section for example, CS-1, have many subjects. But if I did many-to-many, when I assign a teacher to a subject, the subject doesn’t have reference to what section it belongs to. Unless I made it three-way-m:n relationship. Where a table contains user_id/teacher_id, section_id and subject_id.
mkay.
So a course has many sections, which itself contains many subjects (you’ve already defined 4/5 tables at this point - course, course-section*, section, section-subject, subject). [EDIT: course-section is debatable, because you havent said that a section can belong to multiple courses.]
A teacher would not teach a subject, because presumably different teachers teach a given subject to different sections. Thus the teacher id would go into the section-subject table. (this is part of the reason why section-subject is its own table, the combination of a section and subject is its own unique entity, with unique properties.)
A student, likewise, enrolls in a given subject in a given section (their assigned section?), but there would be many students in a given subject in a given section, and a student should be enrolled in many subjects in their section, thus you need an enrollment table such that it joins student (or person) and section-subject.
EDIT: UNLESS students are enrolled in a subject by nature of their being in a section, in which case the link is one-to-many between section and person.
Hmm. So I should make a intermediate table called section_subject then that table is connected to another intermediate table that has the section_subject_id and the user/student_id ? Is that allowed?
Your database has no concept of ‘intermediate table’. It’s just ‘a table’.
If i called section_subject “class” (in that a class teaches a given subject to a given section), I haven’t magically changed the definition of the table - it’s not ‘intermediate or not’, its an entity with its own properties (what subject is being taught [one to many], what section it’s being taught to [one to many], who its being taught by [one to many], maybe what time on what day its being taught [one to many], what room it’s being taught in [one to many], and the students that are enrolled in it [many-to-many, means i need another table between this entity and students])
This makes no sense to me, and I think where the convolution of relationships is occurring. What kind of a school is this? It seems in some senses to be like a college, and in other ways it would seem to be like a middle school (in US terms of course). Which one is it?
And to hijack my own example.
I might create that extra table, then think about what i’m doing with it - if the enrolled students in a class are “all the members of the section”, then i’ve created a redundant table - I can already determine which students are enrolled in a class by following the link from Class → Section → Student (or Person, or whatever you call that table).
If they’re not ALL of the members of a section, then I need some way to differentiate between the students that are and aren’t enrolled in my class.
(I, like Dave, am playing in the dark here, having been educated in the US. This system sounds eastern-hemisphere to me.)
Both, the school both have high school and college.
Oh sorry, I misunderstand it, I thought it was a pivot table.
Hmm… So the Class/section_subject table contains, user_id, section_id, subject_id, and other data like room_id, schedules… etc? I’m sorry if I’m redundant, my brain just can’t… 
well it might contain those things. I don’t know your requirements, it’s you that has to decide what goes into the table or doesn’t, because you know the structure of the relationships.
This is where drawing a diagram helps. Preferably one that you can change and edit, rather than one of pen on paper. There are many tools out there that can help you draw an Entity Relationship Diagram (ER Diagram, or ERD).
Does the Class have 1:m relationship to all the said tables? (section, subject, user). Student can have multiple sections though, doesn’t that mean it can also have multiple classes?
You tell me. You would know.
I keep mixing Section and Class . Class is the combination of all the necessary tables. Section is just the actual section.
I very highly agree with @m_hutley. You would do well to draw up a “blueprint”, known as an ERD (Entity Relationship Diagram).
This is just a random example I grabbed from the net. I have not reviewed it for correctness.
Can I do this? Is this allowed?
The id of the pivot table is used in Class table.
