I fondly remember as a child playing with blocks. One toy in particular rings out to me. The match the block to the hole in the box. It was never actually challenging but I never actually wanted to play by the rules. I always wanted to find a way to make the blocks go into different holes. Now when I use data bases it reminds me of having that box in my hand trying to jam the circle peg in a square whole. Maybe the circle didn't fit but a lot of other did fit in other holes. Giving at least in my young mind a Many to Many relationship.
Many-to-many relationships
What is a many-to many relationship? Its often found in data bases, where one table has multiple pieces of data related to another table of data. The most common example I keep running into is the student VS. classes exp. Where a college has a data base of students and a data base of the classes the students are signing up for. So in this instance there are multiple classes and multiple students, but also there are multiple students signing up for each class. As well as each student has to sign up for multiple classes.
So in order to organize this data you need multiple data points, a place to store those points. As well as sort and export those data points. By using a table or graph we can see how this works.
Table Handling
So how do we organize our data, well we will need to make a table to store our data in. Since we have at least 2 tables of information we have to figure out what our relationship is and what directions the data will take. Where is it coming from? Where is it going? Also how many or how often data points are taking that route? We will have to draw this out. We can do this in three main ways
One-to-one: one piece of data in one table is related to one piece of data in another table.
One-to-many: one piece of data in one table is related to many other pieces of data in another table.
Many-to-Many: Multiple pieces of data are related to to multiple pieces of data in another table.
When you want to handle a one-to-one or one-to-many relationship, you can do this by using a Primary Key of one table in the other table as a Foreign Key. while a many-to-many relationship its different, let's see how that might look.
The Many to Many Table Handling
So since our rules look a bit different in the case of Many-to-Many relationship tables we have to change it up a bit. The student has Many classes and the classes have many students. We wont be able to add a primary key of one table into the other table. That would mean were only storing a single relationship, and we want many relationships. So we accomplish this by using a concept called a joined table. We are starting out with 2 tables of data and Joining them.
Table 1:
| Students table | "Primary Keys" |
| Students Name | Students # |
| Augusta | 1 |
| Collins | 2 |
| Diaz | 3 |
| Jones | 4 |
| Smith | 5 |
| Dover | 6 |
| Quinn | 7 |
Table 2:
| Classes table | "Primary Keys" |
| Class # | Class Name |
| 10 | Algebra |
| 20 | Chemistry |
| 30 | Physics |
| 40 | Psych |
| 50 | Comp Science |
We are adding a third table, that will join our data. It looks a lot like when you pick 4 numbers and come up with all the possible outcomes. Instead we are taking the 2 tables of data and putting a 3rd table to store the outcomes of the students classes eventually giving us the rosters to each class . This will become our Many-to-Many relationships.
Table 3
| Class Roster table | "Foreign Keys" |
| Student # | Class # |
| 4, 5, 1, 3 | 10 Algebra |
| 2, 1, 4, 6 | 20 Chemistry |
| 5, 2, 6, 7 | 30 Physics |
| 3, 4, 6, 7 | 40 Psych |
| 1, 5, 2, 3 | 50 Comp Science |
The outcome
This allows us to keep 3 separate tables of data where our student and class tables remain the same and our class roster table stores the data for the students enrollment. What were looking at here is 2 tables of Primary keys that join into one table of Foreign keys. This will give us the ability to add even more data to each table in the long run. As well as add more relationships over time such as time of day or the room numbers and or Professor names. This provides us a way of creating an organized easy to navigate data structure. Giving us a vivid picture of the structure to help create our algorithms to sort store, and retrieve this data.
This example is only one way you can utilize this method. Many-to-Many Relationships can come in many shapes and sizes. With so many options its hard not to utilize this method. I implore you to go out and find the method that speaks to you the best.
sources and inspirations
These are some other wonderful blogs and content that helped me understand the concept and furthered my knowledge. I hope they can help you just the same.
Karen Warmbein, Many (to many) relations among the stars. "https://medium.com/swlh/many-to-many-relations-among-the-stars-1728ba18a2d0"
eTutorials, Establishing Each Relationship "https://etutorials.org/SQL/Database+design+for+mere+mortals/Part+II+The+Design+Process/Chapter+10.+Table+Relationships/Establishing+Each+Relationship/"
James Virgo, Relationships in SQL - complete guide with examples "https://www.dittofi.com/learn/relationships-in-sql-complete-guide-with-examples"