Please help me understand further the many to many relationship. Suppose I have Teacher and Students Table and in my understanding it is one-to- many.
Teacher
TeacherId Primary Key
TeacherName
Student
StudentId Primary Key
TeacherId Foreign Key
StudentName
TeacherId can appear several times in the Student Table.
I'm confused because Student can have many teacher.
- When can I say something is
one-to-many and when is it many to many?
I can say One Student can have many teacher.
On the other hand, I can also say that One Teacher can have many student.
- If both of one teacher and one student can have many students(for teacher), many teacher(for students). How should I construct the table(s)?
- Is Student-Teacher relationship or vice versa can be considered
many-to-many?
I understand that once I've properly identified the relationships, it would be easier for me to decide how many tables to create or whether or not there's a need to create a join table. From the tutorials and information I read online, it says that I should create a join-table if the relationship is many-to-many. So I thought it should look something like this.
Teacher
TeacherId Primary Key
TeacherName
Student
StudentId Primary Key
StudentName
Teacher_Student(join table)
Id Primary Key
TeacherId PRIMARY KEY
StudentId --took PKs of both Student and Teacher table.
Are there conditions to easily identify relationships, maybe if it's dependent on one table or not?
I'd appreciate any explanation. I just started learning this.
Thanks.
I think easiest way to identify a one-to-many relationship is to think of it logically for example one book can have many pages but one page can not have many books. With your example of students and teachers you are right to assume that this is a many to many relationship.
The table construct should be as you have listed above, one table for students and teachers - with all metadata e.g. name etc. Then you can use a link/join table to turn your many to many relationship into two - one to many relationships.
On a side not your above example lists the TeacherId as a primary key in the Teacher_Student(join table) which is wrong - this should be either two foreign key joins with a primary Id field or a composite primary key between both teacherid and studentid - this removes the need for an Id field on the join table.
- Yes this relationship can be considered a many to many for most cases, because thinking about it logically one student can have teachers for different subjects and one teacher can have multiple students in one class.
A case could be made that there could be some special cases were a teacher only has one student or a student only has one class - these can still be modeled with your join table so it would be best to handle all cases then design a limited table structure which can't be expanded.
Hope that helps ^^
Normally many to many relationship is identified on ER diagram
to illustrate the relation in a good manner so that you don't need to create table for many to many relationship
you can create for example Course table
with these attributes (CRN number normally integer, Student Id, Teacher Id, subject, day and time
Note: Day and Time is optional I'm just clarify for you how it will be
I wish that I could help you :)
A table has two Foreign Key field like Teacher_Student table where
StudentId Foreign Key
TeacherId Foreign Key
