junction/bridge table for many to many.

why we need junction table for many to many.

i am not clear on that concept. can you please elaborate with example tables with data?
i am going through below link but not completely clear
https://www.youtube.com/watch?v=P_nhBKs25DQ
please advise
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilcoxonCommented:
Say you have two tables people and activity.  How do you relate one to the other?  If you know there are never more than X activities per person, you could add columns into the people table to indicate what those are but what if that changes (either due to tracking new activities or someone just over-commits themselves to more than the "max")?  The best relatively simple way to handle it is with a junction table which is fully extensible.

quick possible schema of people_activity_xref table: people_id, activity_id

You could add additional columns to the xref table if necessary (such as valid time fields).

Does that make it any more clear?  If not, can you ask a more specific question?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
There is a lot of information out there on many-to-many relationships that can probably explain it better than we can here:
https://en.wikipedia.org/wiki/Many-to-many_(data_model)

I agree that if the above post or any of the links on the web doesn't clear it up for you:  What still isn't clear?
0
Pawan KumarDatabase ExpertCommented:
Hi Gudii9,

For many to many relationships we have to create a third table ( M : M --> 1 more table )
Example Student and Teacher
Student ( StudentId, Name , ...)
Teacher( TeacherId, Name ...)

Now one teacher can taught multiple students..and one student will have multiple teacher for multiple subjects...so they have Many to many relationship

In these kind of cases we need to create the third table so that all our DML's will be smooth( DELETE, Insert, Update and SELECT ).

So for this example the third table will be StudentTeacher(StudentTeacherId , StudentId, TeacherId, SubjectId , Year )

So the third table will have primary keys of both the source tables in the this case StudentId and TeacherId, now the subjectId defines which subject the teacher is taught  the student. Another important column is Year. Which year they are learning that subject.

For Subject table we can have SubjectId, SubjectName...startingyear..etc as columns..

I hope things are bit more clear now.

Good luck.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gudii9Author Commented:
So the third table will have primary keys of both the source tables in the this case StudentId and TeacherId, now the subjectId defines which subject the teacher is taught  the student. Another important column is Year. Which year they are learning that subject.

For Subject table we can have SubjectId, SubjectName...startingyear..etc as columns..

is subjectid is primary key in this new third table?

So for this example the third table will be StudentTeacher(StudentTeacherId , StudentId, TeacherId, SubjectId , Year )
is StudentTeacherId is primary key in this new third table?

how this third table makes DMLs smooth to insert , select etc?
0
Pawan KumarDatabase ExpertCommented:
>>is subjectid is primary key in this new third table?
No

 StudentTeacher(StudentTeacherId , StudentId, TeacherId, SubjectId , Year )

The Primarykey of the third table will be StudentTeacherId .StudentId and TeacherId are foreign keys here. They are primary key of their respective tables.

>> is StudentTeacherId is primary key in this new third table?
Yes.The Primarykey of the third table will be StudentTeacherId

>> how this third table makes DMLs smooth to insert , select etc?
When ever the student enrolls for a course and assigns a teacher we will insert the data in this table. So when you need the data we need to JOIN all the three tables to get all the details.
0
wilcoxonCommented:
As in my example, you do not need a new, separate primary key I'd field.  So, StudentTeacher would only contain StudentId and TeacherId (and the primary key would be both those fields).  Unless you need another table to reference the junction table then there really is no reason to add another new id column.
0
gudii9Author Commented:
>>is subjectid is primary key in this new third table?
No

is it just regular column if not primary key column?
any end to end example or link or resource to understand this DML operation using probably some java code as i am a java guy?

any good free video tutorials on this to comprehend better?
0
wilcoxonCommented:
A simpler example would remove the Subject table (and the SubjectId column from StudentTeacher).  Subject is just another table (with SubjectId as a primary key).

It may be clearer and cleaner to define StudentTeacher without StudentTeacherId.  So StudentTeacher becomes StudentId, TeacherId, SubjectId, Year and the primary key is all of those columns (or possibly all of the columns except Year depending on if the same student can study the same subject with the same teacher in multiple years).  StudentId, TeacherId, and SubjectId are each also foreign keys to their appropriate tables.  In general, it is often a better idea not to create a new primary id column unless you will need to reference the junction/xref table in yet another table.
0
Pawan KumarDatabase ExpertCommented:
>.is it just regular column if not primary key column?
This combination --> StudentId, TeacherId, SubjectId and Year - defines uniqueness meaning unique key..
StudentTeacherId - is a just a number column which will get incremented when we insert a row in this table. This column will be used when we insert the data so that data will be in sequence.

>>any end to end example or link or resource to understand this DML operation using probably some java code as i am a java guy?
any good free video tutorials on this to comprehend better?
You can refer this youtube video -https://www.youtube.com/watch?v=mmYy81EOVOo
0
slightwv (䄆 Netminder) Commented:
>>Example Student and Teacher

Even though I think this is a bad example because you would probably never create this relationships, let's go with it.

@gudii9,

If you didn't create the third table to resolve the many-to-many, you would need one column for every possibility in the other tables.

Take the Student table:  How many teachers can a single student EVER have?  If you say 100:  Then the student table would need 100 columns to store every teacher id for that student.

Then what happens when you get that one student that has teacher 101?  You need to add a new column to the student table and change ALL the sql that references that table.

See the problem with not using a 3rd table?

For what it's worth, I'm not a fan of an artificial primary key on my intermediate tables.  If designed correctly, they have a natural key.  Use it.
0
Olaf DoschkeSoftware DeveloperCommented:
Are you clear about 1:n relationships? If not, the key to understanding n:m is first understanding 1:n relations.

The need is clear from the outset: Starting with two tables any of them can only have one foreign key, which only points to one other record. Foreign keys on both tables also can't solve the problem.

What do you need? You need to store pairs of keys, as many as there are relations, not only from this record to another one, but from any to any record. The only way to easily do that is to store those key pairs in a separate table. That way the n:m relation is split into two n:1 and 1:m relations.

Bye, Olaf.
0
wilcoxonCommented:
Split points evenly across all posts that added to the conversation (eg first post picked as best and all other contributory posts marked as assisted).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.