How to Correctly Define Many-To-Many Relationships in Database Design
ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- 📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
A many-to-many relationship is where many records in one table can relate to many records in another table.
In this video, you'll learn:
What a many-to-many relationship is (with an example)
Why a many-to-many relationship has issues with modeling like a normal relationship
The solution to the problems caused by this relationship
How to update your ERD (Entity Relationship Diagram) to include the new solution
Timestamps:
00:00 Introduction
00:30 What is a many-to-many relationship?
01:27 Student and class example
02:34 Solution to this problem
03:18 Design of the tables
05:47 Name of the table - วิทยาศาสตร์และเทคโนโลยี
![[TH] VCT Pacific Stage 2 - Week 1 Day 2 // GEN vs T1 | PRX vs TLN](http://i.ytimg.com/vi/ZkFjjJNwzH0/mqdefault.jpg)
this channel should be a million-subscriber channel, developers overlooked the importance of the database
Thanks for the support!
Yes, and most piece of software that need a database also means are database-centric. that is, they are 'data-driven' but despite this, as you said is overlooked and non optimized which is ridiculous. Furthermore, data for most company is probably the single closest thing to their actual 'richness' or 'money' That is, data == money and database == bank from a Company point of view, so see what disaster company do with their money/bank (data/database) is crazy. Once, because of a negligence of some manager in a previous company, and entire database was gone., The guy lost job on the spot and company lost lots of money..
It’s crazy that I just learned more in 3 min than I did in 2 weeks of a lecture
Awesome! Glad you liked the video and learned something from it.
Me too. :)
in my opinion this video serves two purposes:
1 clear and simple explanation for those who need to learn its subject
2 learn the standard of a high quality video tutorial (for SQL or else), many skilled vbloggers fail at it
thanks for posting
Thanks, I'm glad you like the content and the style of video!
Man, this completely saved my day. I was banging my head against this concept until I saw this. Thank you.
Glad it helped!
You prolly dont care but does anybody know a way to get back into an Instagram account?
I was stupid forgot my account password. I would appreciate any help you can offer me.
@@markusandrew8360 what about the ,,forgot my password" option?
It's not my job to judge but I have to say something on this one. Like Jack Williams said below, a lot of people are going to learn more in this 7 minutes than in 2 weeks of lecture. Your order of revelation was perfect (and a whole lot of even well seasoned instructors get this so very wrong). Your simple yet totally accurate graphics were perfect for the task. Your narrative and tone were perfect. Your CSV example and the mention of how that violates several normal forms was perfect because it expressed the problem without making "normal forms" a distraction from the gist of the presentation.
This should be "required reading/must watch" for all instructors that want to learn more on the arcane subject of how to make an effective presentation. The title and write up on this video are equally impeccable. Very, very well done Mr. Brumm and truly deserving of the "Database Star" handle.
If someone asks me about many-to-many joins, junction tables, or just how to solve such a problem, this video is where I'm going to point them to. BOOK MARKED!
Wow, thanks so much for the comment. I’m glad you found this useful, not just the topic but the video structure overall. I’ll keep this in mind for videos I create in the future. I’m glad it was helpful for you and thanks for sharing it with others!
Thank you for the help. I was stuck with this exact problem and couldn't understand where a certain table came from and then saw they just broke up a many to many relationship. Much appreciated! With distance learning videos like this help out a lot!
No problem, glad it was helpful!
You have some serious teaching skill. Keep up the great work! Your video's helped me with a current design I'm working on, involving films. Films often have multiple genre's and genre's can of course be associated with multiple films. This video helped me clearly decide to use a joined table. Thank you!
Thanks so much! Glad you like the video and glad it could help you with your design.
Its a great example of the greatness of Proper and correct way of lecture.
Thanks!
This channel is so underrated
Thanks Ric!
i agree man these tutorials are a blast.
YOU MADE MY DAY WITH THIS VIDEO. THANKS SO MUCH! PLEASE, DO NOT STOP!
Glad to hear it!
Best video I have seen on Many to Many relationships. Precise and concise. Thanks a lot.
No problem, glad you found it useful!
Thanks you, I had a very hard time understanding why the many-many relationships needed a bridge table and your explanation was very helpful.
Glad it was helpful!
🌞 Thank you, it is clear as day and now I understand the concept of Many-to-many! Yayyy!
Glad it was helpful!
This is a real good example helped me alot, as I was stuck how to make many-to-many relationship. Thanks greatly appreciated!
Glad it was helpful!
This was a really good explanation, and it really helped me in my examination, so thank you so much!
Glad you found it useful!
Thanks for the tutorial!
Thanks, glad you like it!
can't believe this content is for free .. this is even better than paid stuff
Thanks!
Struggled grasping this until I found your video; thanks.
You're welcome!
Very helpful. The joining table was the kicker for me :) THX!
You're welcome!
Thank you so much, you make all details so clear and easy to follow
Glad it was helpful!
Awesome. This was exactly what I was looking for!
Glad I could help!
Thank you for this knowledge. I finally get it.
Glad it helped!
Thank you. Just what I needed
Glad it was helpful!
Thanks so much. Made understanding this so much easier.
Glad it helped!
Thank you for the video. I am grateful for your time and contribution. Kind regards, Akira.
You're welcome!
So much gold !!! Thank you.
Glad you enjoyed it!
Awesome! Best explanation on this topic for me. Thanks.
Thanks! Glad you liked it.
This guy is the DBMS God.
Thanks Raghav!
your explanation is so simple and so clear
Glad it was helpful!
Excellent. very clear explanation, liked and subscribed.
Glad you like it! And thanks for subscribing!
solid af, straight to the point most appreciated !
Thanks, glad you liked it!
wow ive benn so confusing on designing db with many to many relationship, thank you for the nice explanation
Glad it was helpful!
Understood. Thanks for the tutorial.
Glad it was helpful!
Oohhh that what my tutor was trying to teach me today. Thanks I now understand
Glad it was helpful!
Thank you for your expaination. Helps me to understand the data duplication.
Glad it was helpful!
Holy, this video is just pure gold
Thanks!
thanks Ben , your explanation is short and full of details 🌹🌹
Thanks, glad you like it!
Amazing video. It is so simple after watching it. Good luck!
Thanks so much. Glad you liked it.
Very good explanation, thanks!
Glad you liked it!
Great content! Thank you!
Glad you liked it!
Thanks for the video.Love it!
Glad you enjoyed it!
Thank you for posting this video
You're welcome
This was quite helpful. Thank you :)
You’re welcome!
thank for your sharing
No problem, glad you like it!
Thank you I have an exam coming up and this really helps a lot!
Thanks, glad you like it! Good luck for your exam.
This is very good.
Glad you like it!
thanks, it helped my a lot
Glad you found it useful!
it was really nice the explanation abouth the options of using or not the primary key in the student_class. Ty
Thanks, glad you like it!
This is awesome. Thank you
Glad you liked it!
Great video; simple example 👍
Thanks!
Very good tutorial....
Glad you like it!
Nice explanation, thanks.
Glad it was helpful!
Thanks !
[ fastest way to bring back to my memory data base design I did few decades ago :) ]
Γ
Glad it helped!
Thanks...This is so helpful
Glad it was helpful!
Thank you!
You're welcome!
very clear and understandable explanation. greetings from germany
Thanks!
Great content
Glad you like it!
Thanks for explaining this way more simply and eloquently than my university materials lol
No problem, glad it was helpful!
Thanks alot for a good video!!
Glad you liked it!
thank you, GOD BLESS
You’re welcome
Thank you very much
You're welcome!
Nice video thank you! Just wanted to mention that as someone who would be new to the database. I would prefer the StudentClass table name as it would be easier for me to find it. I have experience working with big database and often I find myself looking at a new table and then trying to find all the relative ones. Lets say if I am looking at Student table, StudentClass would have been the next table that I find, and then Class is already next.
Good point! Good to know that you would find that name more helpful.
Great Channel
Thanks Ronald!
Thanks!
No problem!
great explanation
I'm glad you like it.
clear explanation!
Thanks, glad you like it!
@@DatabaseStar can I apply this to many-to-many for the same kind of tables.
For example, student-student (friendship) where multiple students have multiple students as friend.
Is it best way?
Thank you so much omg
Glad you found it helpful!
Thank you so much for such a great video.
I'm always stuck on these relationship things..!!
You earn the sub and like :)
And I would like to suggest you that if you grab all the related videos and make a playlist or put a link in the description
It's all scattered actually I didn't find this video until TH-cam recommended me while I was watching your one-to-one relationship video..
And also it would be great if you can make same videos on a full project example like by using Entity framework core.
Anyway I love your teaching style thank you again..!! :)
Thanks a lot, and I'm glad you enjoyed it!
Sure, that's a good idea about the playlist. I'll make one and put it on the channel.
A project example sounds good. I don't have any experience with Entity framework core but I can use something else.
Great Video
Thanks!
thank you
You're welcome
Why tf do teachers ( 5 of my teachers ) not teach this way, thanks for the video life saver
Glad you found it useful! Do you have any other topics you’d like to know more about?
That’s a shame your teachers aren’t helping that much.
thanks for this video. I am now wondering 1. Can I set this "Many-to-Many" relationship up in my Oracle 18.4XE DB and 2. How? Obviously, I am totally new to Oracle and setting up and working with DB's.... Help! Also in what order of classes do I need in order to be proficient in this area of IT?
Yes, you can set it up in Oracle 18 XE (and any other database you happen to use). I would suggest learning about database design first, and then an introduction to Oracle SQL. I have a couple of articles that may help, or you can Google for something you like:
www.databasestar.com/database-normalization/
th-cam.com/video/lzzAXsySxx4/w-d-xo.html
Thank you :-D
You're welcome!
Great video, thanks! How would you write a SELECT statement to get the list of class names one student was taking? For example, at 5:40 looking at your example, it looks like student Claire is taking three classes (DB01, PH01, WEB02). Any advice on what the select statement would look like when a joining table like this is needed to get ? Thanks again!!
Thanks! Sure, the SELECT statement would select from the class table and use some joins to the student_class and student table. Here's one way to do it:
SELECT name
FROM class
INNER JOIN student_class ON class.id = student_class.class_id
INNER JOIN student ON student_class.student_id = student_id
WHERE student.student_id = 4;
This will show all class names taken by Claire.
@@DatabaseStar thanks!!
Thank you for this video. Very nicely explained. Can you please confirm/explain if there is a difference between "Joining Table" or "Look Up Table" or are they the same?
Thanks! Yes there is a difference. A Joining Table is used when there is a many to many relationship and it's for storing the different combinations of records, like in this video. A Lookup Table is to hold a single list of options - think of something like options in a drop-down list on a web page.
Very clear explanation, I just created tables students and classes. and I tried to insert data. and try joining the query where is who is taking class 1 and what classes are taking student 1. thanks 🙏🙏
Thanks! I’m glad it helped.
thanks alot
No problem!
you are super good
Thanks!
Thanks so much for one of the best Database videos I have ever seen. Thanks for spending the time to make things super explicit through detailed and well-explained examples! I have 1 question though. What do i set as the primary key for my linking table? I am using SQLAlchemy and it requires a primary key on each table. Would it be appropriate to set an index (like 0 1 2 3 4 5...) just to satisfy it, no to use? Thanks in advance!
Thanks, I'm glad you like the video!
If you need a primary key, I would recommend that you create a new column for the primary key on the linking table. This could be an auto-incrementing number, as you mentioned (the numbers would go 1, 2, 3, 4...)
Hey thanks for your invested time! I have a question, is it necessary to use two foreign keys? Or can I use only the student Id as a foreign key inside the class table? Thanks!
You’re welcome! Yes you would need to use two foreign keys in the joining table so you can capture all of the combinations of student and class. If you just have the student ID in the class table, then you won’t be able to store multiple students for a class.
A joining table seems a lot like a fact table in the dimensional modelling paradigm. It's populated mostly with foreign keys and (optionally) some measures. Is it correct to think of a fact table as a joining table then?
Yeah that's one way to think of it! They are quite similar as you've pointed tomorrow.
This is amazing, i have a similar relationship like this at work in our project. We have a relationship of contact and contact-list. One problem we get is when we need to fetch all contact lists along with the number of contacts belonging to that list (done using grouping in mongodb aggregation), everything is good except the edge that when the contact-list has no related contacts, the contact-list wont even show up in such a query. Any workarounds?
I would guess that this is due to the join type being used. If it's an Inner Join, between the two tables, it will only show matching records (contacts that have a contact list). If you use an Outer Join, it would show contact lists that have no contacts. Hope that makes sense. I have videos on Inner and Outer joins, and you can search TH-cam or Google for others too.
Can you elaborate on the the relationship between the entities...like what crow's foot notation you would put between each entity and why. Thank you!
Sure! I believe they would both have "one to many" relationships, where the "many" side (the one with the three lines) goes on the joining table in the middle, and the "one" side goes on the other tables.
Great Video!
I have a doubt. You said while linking the student and class table you can't add more rows (for the same person) as it violates the normalisation constraints. But in the final joining table, you made multiple rows for a single student id. Isn't that a vioalation ?
Thanks! Yeah, that's right. Ideally you would only want to capture one combination of a student and a class (e.g. student John can only be in Maths class once). You can add multiple records for the same student, but the classes would be different (student John could enrol in Maths, Science, and Sport, for example).
@@DatabaseStar Okay I get it. And btw thanks a ton for the prompt reply on video thats slightly old (atleast by TH-cam's standards).
subscribed this channel👍🏻
Thanks!
Would you be able to let me know: We could use a composite primary key for the joining table. Hence, a composite primary key would be unique by definition right?
Thanks
Michael
Hi, yes you can use a composite primary key for the joining table. It works pretty well and you won't have to create a third column for the Primary Key.
ty
You’re welcome
Great
Thanks!
I have a question: in the diagram you created showing the crow's foot notation, there is only one notation mark on each end of any relationship. To my understanding, there are supposed to be two notations on each end at all times. Thank you.
That's true, you can have two notations or marks. One represents the minimum and one represents the maximum.
Hello Ben! Thank you for this video. Could you shed some light on this please: I get the purpose of joining table. However, I'm having trouble wrapping my head around the relationships between (student and student_class) and (class and student_class). Why both of those are one to many?
Glad you like the video! Sure I can help.
The reason they are both one-to-many is because that's how a many-to-many relationship is captured. And the reason for this, with this example, is because a student has many classes. So, in the student_class table, for student id 1, there could be multiple records (student 1 class 1, student 1 class 2, student 1 class 5, for example). But for each record in the student class table, there is only one related student record.
We can say the same thing for the class and student class relationship.
Hope that answers your question!
So are bridge tables helping join Dimension tables or Fact tables? Are there any books that dive deeper into this?
Bridge tables are used to join two tables where there is a many to many relationship, in a normalised or "OLTP" database. I don't have as much experience using Fact or Dimension tables, but as far as I know there is a one to many relationship between Fact and Dimension, so there is no need for a bridging table.
As far as books, I'm not sure. There may be some books on data modelling or database design out there.
Nice vid! One question though. I cannot add any data to my joining table, am i thinking wrong here? :) thanks
Thanks! You should be able to add data to your joining table with an INSERT statement. If both columns are foreign keys, these values need to exist in the corresponding table. Are you getting an error?
@@DatabaseStar I guess, @durrium means that we won't be adding the data explicitly to the joining table? That is what I was thinking. Maybe you can have stored procedure or something for automatic population of the joining table.
3:19 what software is being used here?
It’s called Lucidchart
What would be the SQL query to generate the student_class bridge table? Something like: SELECT * FROM student CROSS JOIN class?
That query would show all students in all classes. To populate tables like that in examples I usually use spreadsheets and insert the data manually. Or you could write some SELECT queries that selected specific students for each class.
05:10 I don't understand that part. If I set unique const. for table I should do that for each column right? but now we can't assign same class id for other students or vice versa.. How to prevent create duplicate record for both student and class ids?
Good uqestion. We can add a unique constraint for multiple columns in a single constraint, and that's what I am suggesting here. Something like this:
CONSTRAINT uc_studentclass UNIQUE (student_id, class_id)
If I were to build tables for an English dictionary, would I define tbl_term to tbl_parts-of-speech as many-to-many and then tbl_parts-of-speech to tbl_definition as many-to-many? Thanks
That sounds good to me. As long as a term has many parts of speech, a part of speech has many terms, a part of speech has many definitions, and a definition has many parts of speech.
Thanks a lot man 🙏 u r awesome
I have a question if i may,
Sometimes Database is not as simple as just one relationship,
For example :
What if u have a product and that product has multiple color's and every product with a certain color has multiple sizes,
So in this situation the color table has many-to-many with the product, and the size table has many-to-many
With the product and color which is confusing, because
U cant say product-1 has three sizes because the size depends on the color first,
What i mean by that is in the real world its gonna be like this :
The PRODUCT-1 with the color RED exists in three sizes
XL quantity 5
L quantity 10
M quantity 7.
So how is this kinda relationship looks like???
Im sorry i know its a long question
But i would really really like to see a video like this because all i could find is just the basics and u cant learn much out of that
Great question! I would imagine the product table would have a column called size and a column called colour. The size column in the product table could actually be size_id, which refers to the id in the size table. Same for colour - colour_id in the product table refers to the ID in the colour table.
You could then have an entry in the product table for each combination of size and colour. But then you would have duplicate products. So perhaps you need a table that captures the combinations of size, product, and colour.
I can create a video on this to better explain it!
@@DatabaseStar yes thats exactly what im strugling to understand because if i wanna follow the normalization rules redundancy is a bad design
And thank you man for considering making a video 🙏
@@jacknoyan9595 Is the video available?
@@shegerians no it never came out
At first i was afraid of this "many to many" after this video i realised i implement it correctly for all this time LOL
Hehe glad to hear you're doing it well already!
The explanation is very good, I just have one query, if you just have a list of students and you want to get all the common class of those students...? knowing that in java you don't have the student_class entity
Thanks! To get that, you could write a query that selects the class names and a count from the class and student class tables. It could show the classes with the highest count of records in the student class table.
@@DatabaseStar Of course I understand, but class stdinate table, it is only mapped in the student class, it is not a class as it would be done to consult it in a query with jpa.
Hi Ben, nice video, thanks. However I do not understand you say in 0:28 "how to avoid it..." I did not get the idea why we should avoid it. I think it is not possible to avoid it or replace many to many relationship. How did you mean it please explain more. You describe only how to use correctly the bridge table and that is it. What I know we could use also composite key. I think many to many is not possible to avoid fully. It is simply such a type of relationship. Each more advanced DB has its relationship and I think there is no point in getting rid of it. Please have mercy on my understanding, maybe i only do not realize the meaning if english word "avoid". thank you.
Good question! Yes, it's not possible to avoid them - if you have a "many to many" relationship betwen two entities, you'll still have the concept of that relationship after this process. The way that you capture the data for this is by adding this bridge table. This means you can record all of the combinations that you need between the two entities. Hope that answers your question!