Show menu
Skip ad

MySQL 9 - Many-to-Many Relationship

Thanks! Share it with your friends!

You disliked this video. Thanks for the feedback!

Added by tomm
4 Views
In the last video we had an example of a one-to-many relationship for an application that allows users to create listings of items they are auctioning off. A user could post as many listings as he wanted, but a listing could be posted by only one user. This design is slightly limiting though. That's because there is a real possibility that a user could want to share the sale of an item with multiple people. This video is going to discuss that design. Which one is the best design? That is a decision that is either up to you, or whoever gives you the requirements of the database. This gives us more capabilities with the cost of added complexity.

So we have users, and we have a listings table.

We figured out how to design this as a one-to-one relationship by simply referencing the user in the blogs table. What if we need to reference two users? Well, we have a few options. Let's go through them. The first is to store multiple user_id's in one column. The problem with this is that the data is no longer atomic and we would have to store it as a string. That is a very bad design. The other option is to create a new column for owner_2 This too is a stupid design because if we have only one author we are going to have a ton of NULLs for this column and we are limited to just 2 owners. The other option is to create a new row that has the other owner and repeats the data about the article. This is bad because now we have duplicate data. If you remember the last video, this is the exact same problem we had trying to store the listing inside of the Users table.

Why are we having this problem? With a one-to-many relationship it works fine because a listing is associated directly with one user. With a many-to-many relationship we are trying to force the many inside of the table that is designed to be on the one side of a relationship. The way we fix this is with an intermediary table.

An intermediary is a table that goes in between the two tables. Essentially we are converting our many-to-many relationship into two one-to-many relationships.

Imagine that this inability to store data correctly causes a conflict between these two tables and the intermediary table comes to the rescue to prevent future conflict.

We often call this intermediary table a combination of the two tables. We will go with user_listings. By convention I make the first one singular. The columns inside of this table will be two foreign keys. One foreign key will reference the user, and the other will reference the listing.

As for the primary key for this table, you can combine both of these columns and say that the combination of these columns has to be unique.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Support me! http://www.patreon.com/calebcurry

Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter

Donate!: http://bit.ly/DonateCTVM2.

~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~

More content: http://CalebCurry.com
Facebook: http://www.facebook.com/CalebTheVideoMaker
Google+: https://plus.google.com/+CalebTheVideoMaker2
Twitter: http://twitter.com/calebCurry

Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Category
Relationship HOW TO

Post your comment

Comments

Be the first to comment