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.
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..
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
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!
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!
I've watched a few of your videos now. Pure gold! And your PDF's from your website are very useful. Thank you for taking the time and effort to create these.
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 🙏🙏
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.
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.
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/ ruclips.net/video/lzzAXsySxx4/видео.html
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?
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.
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.
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...)
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!
Could you help me understand why the class ID isn't considered a primary key while the student ID is considered a primary ID? Isn't it all perspective? 4:33
I'm not sure why I mentioned here that we don't have a PK on the table on the right (the class table), as earlier in the video it shows the diagram that has a PK on the class table. I think you should have a PK on the class table.
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)
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.
why can we not store multiple ClassIDs per student? is it because there is no array data type in databases? if string is the only thing we can store is it possible to convert array to string and store that value with an understanding that one will need to convert back from string to array before using it?
Good question. Yes, it's because databases don't support array data. You could store it as an array inside a string, however: There is no validation that the class ID value inside the string is valid, because it's not linked to the class table. There is no validation that the class ID is unique, because you can't put a constraint on it. You can't store extra information for that student and class combination, such as when they enrolled, if they left the class, any scores or grades related to that. It's possible to add a class ID value into the array but it doesn't exist in the class table, making it hard to find a match. Hope this helps!
It's a similar concept. You can add the foreign key of the third table into the joining table. I can create another video on this to explain it further.
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 🙏
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.
Good point! The reason is that it reduces the quality of the data in the database and makes it hard to process. At approx 01:48, there's an example of student and class tables. Storing multiple class IDs for a student in a single column makes it hard to find all of the students in a class (e.g. find all students where class_id contains the value of 2), hard to edit the fields (e.g. change John's classes by removing class ID 2 and adding class ID 8), and hard to ensure the values are valid (e.g. adding a class ID of 15 but class ID 15 may not exist in the class table). Hope that answers your question.
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.
Hey, thanks for subscribing! I can answer questions whether you join my classes or not. Feel free to contact me using this page: www.databasestar.com/contact/
@@DatabaseStar i mean i want to make app that could make schedule or task routine for the month but i am not sure how to make the relation since no one tell me the requirements..
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! 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.
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).
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!
Yes I think it can work for some situations. Sometimes you can define dates for when the join happened (e.g. the date a student enrolled in a course) or other fields.
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.
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 RUclips or Google for others too.
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 RUclips 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.
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.
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. :)
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?
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..
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!
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.
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!
This channel is so underrated
Thanks Ric!
i agree man these tutorials are a blast.
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!
Great content sir !
I've watched a few of your videos now. Pure gold! And your PDF's from your website are very useful. Thank you for taking the time and effort to create these.
Thank you so much, I’m glad you like the videos and PDFs. Is there any topic you’d like me to create a video on?
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.
Excellent. very clear explanation, liked and subscribed.
Glad you like it! And thanks for subscribing!
This is very good.
Glad you like it!
This was a really good explanation, and it really helped me in my examination, so thank you so much!
Glad you found it useful!
Awesome. This was exactly what I was looking for!
Glad I could help!
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.
Chief you're THE Database Star. Thanks a ton. God bless you.
Thanks a lot!
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.
This guy is the DBMS God.
Thanks Raghav!
this channel is my go-to learning tool for data related roles interview prep
Thanks!
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.
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/
ruclips.net/video/lzzAXsySxx4/видео.html
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 for your sharing
No problem, glad you like it!
Its a great example of the greatness of Proper and correct way of lecture.
Thanks!
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.
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.
What do you prefer? Daaaataaaa or Dayyyta?
Personally I prefer "daataa" but that could be my Australian accent :)
This guy should have a lot more credit for his job. Thanks millions!
Thanks! You’re welcome
Thanks for explaining this way more simply and eloquently than my university materials lol
No problem, glad it was helpful!
Great
Thanks!
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.
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...)
your explanation is so simple and so clear
Glad it was helpful!
Thank you for your expaination. Helps me to understand the data duplication.
Glad it was helpful!
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!
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!
Could you help me understand why the class ID isn't considered a primary key while the student ID is considered a primary ID? Isn't it all perspective? 4:33
I'm not sure why I mentioned here that we don't have a PK on the table on the right (the class table), as earlier in the video it shows the diagram that has a PK on the class table. I think you should have a PK on the class table.
Finding your channel couldn't have come at a better time. Thank you sharing your knowledge!
Happy to help!
Oohhh that what my tutor was trying to teach me today. Thanks I now understand
Glad it was helpful!
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)
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.
Thank you for the video. I am grateful for your time and contribution. Kind regards, Akira.
You're welcome!
Awesome! Best explanation on this topic for me. Thanks.
Thanks! Glad you liked it.
YOU MADE MY DAY WITH THIS VIDEO. THANKS SO MUCH! PLEASE, DO NOT STOP!
Glad to hear it!
why can we not store multiple ClassIDs per student? is it because there is no array data type in databases? if string is the only thing we can store is it possible to convert array to string and store that value with an understanding that one will need to convert back from string to array before using it?
Good question. Yes, it's because databases don't support array data. You could store it as an array inside a string, however:
There is no validation that the class ID value inside the string is valid, because it's not linked to the class table.
There is no validation that the class ID is unique, because you can't put a constraint on it.
You can't store extra information for that student and class combination, such as when they enrolled, if they left the class, any scores or grades related to that.
It's possible to add a class ID value into the array but it doesn't exist in the class table, making it hard to find a match.
Hope this helps!
Great content
Glad you like it!
Thanks !
[ fastest way to bring back to my memory data base design I did few decades ago :) ]
Γ
Glad it helped!
How about many to many relationships between 3 tables? 4, 5, n tables?
It's a similar concept. You can add the foreign key of the third table into the joining table. I can create another video on this to explain it further.
very clear and understandable explanation. greetings from germany
Thanks!
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 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
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!!
Very good tutorial....
Glad you like it!
Holy, this video is just pure gold
Thanks!
I don't think you told us why we should avoid this type of relationship
Good point! The reason is that it reduces the quality of the data in the database and makes it hard to process. At approx 01:48, there's an example of student and class tables. Storing multiple class IDs for a student in a single column makes it hard to find all of the students in a class (e.g. find all students where class_id contains the value of 2), hard to edit the fields (e.g. change John's classes by removing class ID 2 and adding class ID 8), and hard to ensure the values are valid (e.g. adding a class ID of 15 but class ID 15 may not exist in the class table). Hope that answers your question.
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, I'm a new subscriber. If I join your classes, would you be available to answer direct questions I may have?
Hey, thanks for subscribing! I can answer questions whether you join my classes or not. Feel free to contact me using this page: www.databasestar.com/contact/
I’ll get an email from that form and respond to you.
thank you once again. Learnt a lot from your videos
Glad to hear that!
would you make me databases design for scheduler app with many users that work together in the same day
What do you mean by a scheduler app?
@@DatabaseStar i mean i want to make app that could make schedule or task routine for the month but i am not sure how to make the relation since no one tell me the requirements..
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.
Doubt..hiw a student can have many ckasses..onky one ckasses right?
A student can have many classes if they attend a school that offers multiple classes. They might have one Maths class and another Science class.
@@DatabaseStar ok got it.
I hate to be that guy, but enrol/enrolment? I could overlook it the first time, but after that...
Hey Theo, what do you mean? Is there a better term for this kind of table?
It's spelled Enroll/Enrollment, he was critiquing your typo@@DatabaseStar
3:19 what software is being used here?
It’s called Lucidchart
thanks, it helped my a lot
Glad you found it useful!
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.
Understood. Thanks for the tutorial.
Glad it was helpful!
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 RUclips's standards).
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!
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 asked to me in interview
I hope it helped in your interview
@@DatabaseStar We nornally watch these videos only after being asked😁
it's basically an edge of a graph
Good point!
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!
Amazing video. It is so simple after watching it. Good luck!
Thanks so much. Glad you liked it.
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!
one question: is defining extra columns in a joining table a good database design practice?
Yes I think it can work for some situations. Sometimes you can define dates for when the join happened (e.g. the date a student enrolled in a course) or other fields.
subscribed this channel👍🏻
Thanks!
So much gold !!! Thank you.
Glad you enjoyed it!
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.
can't believe this content is for free .. this is even better than paid stuff
Thanks!
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 RUclips or Google for others too.
Thanks so much. Made understanding this so much easier.
Glad it helped!
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 RUclips 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 content! Thank you!
Glad you liked it!
This is awesome. Thank you
Glad you liked it!
Best video I have seen on Many to Many relationships. Precise and concise. Thanks a lot.
No problem, glad you found it useful!
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.
thanks Ben , your explanation is short and full of details 🌹🌹
Thanks, glad you like it!
Thanks for the tutorial!
Thanks, glad you like it!
you are super good
Thanks!
ty
You’re welcome
This was quite helpful. Thank you :)
You’re welcome!
Thank you I have an exam coming up and this really helps a lot!
Thanks, glad you like it! Good luck for your exam.
Thank you very much
You're welcome!
Thank you so much omg
Glad you found it helpful!
Very good explanation, thanks!
Glad you liked it!
great explanation
I'm glad you like it.
Thank you :-D
You're welcome!
Great Channel
Thanks Ronald!