0

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.

heisenberg
  • 1,784
  • 4
  • 33
  • 62
  • In your last example, teacher_student table can have an `id` as the single primary key and then `teacherid` and `studentid` are foreign keys to their respective entities. You could also drop the `id` field, and use the `teacherid` and `studentid` together as a composite primary key (both still being foreign keys as well). – sgeddes May 15 '16 at 12:29
  • It looks like you already answered your question and understand that answer. If possible, try to understand *why* Teacher_Student exists (perhaps because they're enrolled in a class) and model that object instead of manually creating a relationship outside of the reason. It may be that the resulting object has a different name but the exact same structure. It is still semantically useful. – Jeremy Fortune May 15 '16 at 14:23
  • Thanks all for your comments. I appreciate it. – heisenberg May 16 '16 at 01:18

3 Answers3

1

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

Chris
  • 915
  • 8
  • 28
  • Thanks answering each numbered question. Most of the time it helps me to understand better if every subquestion is answered. There's a part I don't get yet when you say "a composite primary key between both teacherid and studentid" – heisenberg May 16 '16 at 01:23
  • a composite key or compound key replaces the id field with multiple columns that uniquely identify the field for example studentid and teacherid become a composite Id field see the answer here http://stackoverflow.com/questions/1110349/how-can-i-define-a-composite-primary-key-in-sql – Chris May 16 '16 at 10:11
0

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 :)

Ahmed Hillal
  • 59
  • 1
  • 2
  • 11
0

A table has two Foreign Key field like Teacher_Student table where

 StudentId Foreign Key
 TeacherId Foreign Key
Manmohan Kumar
  • 334
  • 2
  • 8