2.6 Many to Many Relationships
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
Already have an account? Sign In »
7 hours 36 minutes
I welcome the module to databases. This is less than 1.6 too many too many relationship. This relationship is gonna be a little more complex than previous relationships because it's going to involve three tables, and that third table is known as a pivot table or a lookup table. And that's not to be confused with the pivot table that you see in Excel.
So let's take a look at this relationship. I've tried to use a pretty sure, for example, and the thing to understand about this relationship as we enter this concept is that the idea is that an employee could belong toe multiple departments, and the department
can belong to multiple employees, and that is
the many too many relationship.
So if we wanted to know what departments Kit Parker belongs to, would take his idea of one head over to the pivot slash lookup table and look for the employee I d. Of one. And we would see that he has to records department idea of one and two.
So we would take that one and two and head over to the department's table
and see that he belongs to the sales department
and the Executive Department. Now let's do this thing. Let's do the same thing for John Smith.
We take his idea of two. We head over to the pivot or lookup table. We see that he has two entries as well,
and he has department I D three and one
and we head over to the department table and we see that he's part of the sales department
and the marketing department.
So we were able to quickly answer which departments these employees belong. Thio going clue this pivot table in the middle and we could see that both employees belonged to the sales department.
Now, how would you handle this with a one too many relationship If you're going to try that? And what's the problem that you run into? Well, let's take a look.
So we're gonna open up the employees table again, and we're gonna open up the department table
now. Here again, we have Kit Parker and John Smith, so let's take the one head over to the apartment table.
He's mentioned twice again. We could see that he's part of the sales department and the Executive Department. Okay, so the same thing for John Smith take the two over there,
we'll see that he's part of the marketing and the sales department,
and we again correctly answered the question of which departments
do the employees belong to.
But you may have noticed something that didn't occur when we were using a pivot table.
And that is
the data is duplicating.
If you look at the sales name,
you'll see that it's mentioned twice,
and that's bad. You don't want duplicating data. It becomes a burden to maintain and develop against. And,
for example, consider if you wanted to change the department name
in this example, you'd have to do that in two places, not just ones.
So you would have to update
record i d four and record I D. One
if you're going to change the department name.
And that violates a concept called Normalization.
And there are multiple levels of normalization. But
a lot of times normalization could be summed up in a common idea that programmers face. And that is, don't repeat yourself. If you're repeating yourself, you're probably
there's probably a better way to do it. And as you can see in this example, we are repeating ourselves sales mission twice
so if we went back and looked at the previous example
that back up there, we can see that sales is mentioned once.
So if we wanted to change the name,
we just do it in one place. And the relationship would hold true because the connecting relationship is in this middle pivot table.
And the next lesson. We're going to install another database that shows this
concept using employees in departments.
And here's the schematic for that database.
Oops, sorry about that. Here's the schematic for that database,
and right here we can see our department's table,
our department imp pivot tables right here
and our employees table. And again we have the one and only one too many
and the one and only one too many.
And that makes sense because we can see the kid has a one too many with. This table occurs many times, and John has a one too many with a stately occurs many times, and that this table has one too many
on this table as it occurs. Multiple times sales.
The department idea one is mission twice,
so that is the the many too many relationships.
I hope you enjoy this lesson and I'll see you in the next lesson.