Reputation: 1964
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.
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.
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.
Upvotes: 0
Views: 3613
Reputation: 434
A table has two Foreign Key field like Teacher_Student table where
StudentId Foreign Key
TeacherId Foreign Key
Upvotes: 0
Reputation: 59
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 :)
Upvotes: 0
Reputation: 925
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.
Hope that helps ^^
Upvotes: 1