Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?
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 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.
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!
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!
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.
I will borrow a great comment - for this great channel - "this channel should be a million-subscriber channel, developers overlooked the importance of the database"
Indeed - it seems that the way Ben explains in combination with his voice makes everything more easily "digestible" pardon me for using this term - fantastic !
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.
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.
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)
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!
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.
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.
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.
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.
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.
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/ ua-cam.com/video/lzzAXsySxx4/v-deo.html
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...)
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.
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 🙏🙏
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.
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 UA-cam or Google for others too.
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.
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!
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.
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).
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.
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.
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!
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 🙏
@@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..
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.
@@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?
Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?
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!
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 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. :)
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!
Its a great example of the greatness of Proper and correct way of lecture.
Thanks!
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.
This channel is so underrated
Thanks Ric!
i agree man these tutorials are a blast.
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?
YOU MADE MY DAY WITH THIS VIDEO. THANKS SO MUCH! PLEASE, DO NOT STOP!
Glad to hear it!
Finding your channel couldn't have come at a better time. Thank you sharing your knowledge!
Happy to help!
I will borrow a great comment - for this great channel - "this channel should be a million-subscriber channel, developers overlooked the importance of the database"
Thanks for the kind words!
Indeed - it seems that the way Ben explains in combination with his voice makes everything more easily "digestible" pardon me for using this term - fantastic !
Thanks, I'm glad you like the videos!
This guy should have a lot more credit for his job. Thanks millions!
Thanks! You’re welcome
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!
Chief you're THE Database Star. Thanks a ton. God bless you.
Thanks a lot!
Best video I have seen on Many to Many relationships. Precise and concise. Thanks a lot.
No problem, glad you found it useful!
this channel is my go-to learning tool for data related roles interview prep
Thanks!
your explanation is so simple and so clear
Glad it was helpful!
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!
Awesome. This was exactly what I was looking for!
Glad I could help!
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!
Awesome! Best explanation on this topic for me. Thanks.
Thanks! Glad you liked it.
Very helpful. The joining table was the kicker for me :) THX!
You're welcome!
This was a really good explanation, and it really helped me in my examination, so thank you so much!
Glad you found it useful!
Thank you for the video. I am grateful for your time and contribution. Kind regards, Akira.
You're welcome!
Excellent. very clear explanation, liked and subscribed.
Glad you like it! And thanks for subscribing!
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.
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.
Amazing video. It is so simple after watching it. Good luck!
Thanks so much. Glad you liked it.
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)
This guy is the DBMS God.
Thanks Raghav!
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!
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.
🌞 Thank you, it is clear as day and now I understand the concept of Many-to-many! Yayyy!
Glad it was helpful!
Holy, this video is just pure gold
Thanks!
Thanks so much. Made understanding this so much easier.
Glad it helped!
very clear and understandable explanation. greetings from germany
Thanks!
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.
Thank you for your expaination. Helps me to understand the data duplication.
Glad it was helpful!
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!!
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.
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.
So much gold !!! Thank you.
Glad you enjoyed it!
thanks Ben , your explanation is short and full of details 🌹🌹
Thanks, glad you like it!
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/
ua-cam.com/video/lzzAXsySxx4/v-deo.html
Thank you so much, you make all details so clear and easy to follow
Glad it was helpful!
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. Just what I needed
Glad it was 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...)
Very good explanation, thanks!
Glad you liked it!
thank you once again. Learnt a lot from your videos
Glad to hear that!
This was quite helpful. Thank you :)
You’re welcome!
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.
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!
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.
Great video; simple example 👍
Thanks!
Thanks for explaining this way more simply and eloquently than my university materials lol
No problem, glad it was helpful!
Understood. Thanks for the tutorial.
Glad it was helpful!
This is very good.
Glad you like it!
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.
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.
Thanks for the video.Love it!
Glad you enjoyed it!
3:19 what software is being used here?
It’s called Lucidchart
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 UA-cam or Google for others too.
thank for your sharing
No problem, glad you like it!
Great content! Thank you!
Glad you liked it!
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.
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!
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 !
[ fastest way to bring back to my memory data base design I did few decades ago :) ]
Γ
Glad it helped!
wow ive benn so confusing on designing db with many to many relationship, thank you for the nice explanation
Glad it was helpful!
Thank you for this knowledge. I finally get it.
Glad it helped!
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.
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 UA-cam's standards).
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.
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.
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!
Thanks for the tutorial!
Thanks, glad you like it!
This is awesome. Thank you
Glad you liked it!
What do you prefer? Daaaataaaa or Dayyyta?
Personally I prefer "daataa" but that could be my Australian accent :)
Thank you for posting this video
You're welcome
Great content
Glad you like it!
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
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..
Nice explanation, thanks.
Glad it was helpful!
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 good tutorial....
Glad you like it!
thanks, it helped my a lot
Glad you found it useful!
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?
Great content sir !
Thanks!
where can i get these slides.
I haven't published the slides anywhere.
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.
Thank you!
You're welcome!
Thanks...This is so helpful
Glad it was helpful!