CODEWITHSUNDEEP
sqldatabaserdbmstable-relationshipsobject-oriented-database
heisenberg
heisenberg

Reputation: 1964

Many to Many Relationship

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.

  1. 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.

  1. 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)?
  2. 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.

Upvotes: 0

Views: 3613

Answers (3)

Manmohan Kumar
Manmohan Kumar

Reputation: 434

A table has two Foreign Key field like Teacher_Student table where

 StudentId Foreign Key
 TeacherId Foreign Key

Upvotes: 0

Ahmed Hillal
Ahmed Hillal

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

Chris
Chris

Reputation: 925

  1. 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.

  2. 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.

  1. 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 ^^

Upvotes: 1

Related Questions