2.6 Many to Many Relationships

00:00

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.

00:20

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

00:38

can belong to multiple employees, and that is

00:41

the many too many relationship.

00:44

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.

00:58

So we would take that one and two and head over to the department's table

01:00

and see that he belongs to the sales department

01:03

and the Executive Department. Now let's do this thing. Let's do the same thing for John Smith.

01:08

We take his idea of two. We head over to the pivot or lookup table. We see that he has two entries as well,

01:15

and he has department I D three and one

01:19

and we head over to the department table and we see that he's part of the sales department

01:25

and the marketing department.

01:29

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.

01:44

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.

01:55

So we're gonna open up the employees table again, and we're gonna open up the department table

02:00

now. Here again, we have Kit Parker and John Smith, so let's take the one head over to the apartment table.

02:07

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,

02:16

we'll see that he's part of the marketing and the sales department,

02:20

and we again correctly answered the question of which departments

02:24

do the employees belong to.

02:28

But you may have noticed something that didn't occur when we were using a pivot table.

02:31

And that is

02:32

the data is duplicating.

02:35

If you look at the sales name,

02:37

you'll see that it's mentioned twice,

02:39

and that's bad. You don't want duplicating data. It becomes a burden to maintain and develop against. And,

02:50

for example, consider if you wanted to change the department name

02:53

in this example, you'd have to do that in two places, not just ones.

02:57

So you would have to update

02:59

record i d four and record I D. One

03:01

if you're going to change the department name.

03:05

And that violates a concept called Normalization.

03:08

And there are multiple levels of normalization. But

03:14

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

03:24

there's probably a better way to do it. And as you can see in this example, we are repeating ourselves sales mission twice

03:32

so if we went back and looked at the previous example

03:39

that back up there, we can see that sales is mentioned once.

03:45

So if we wanted to change the name,

03:46

we just do it in one place. And the relationship would hold true because the connecting relationship is in this middle pivot table.

03:58

And the next lesson. We're going to install another database that shows this

04:02

concept using employees in departments.

04:06

And here's the schematic for that database.

04:10

Oops, sorry about that. Here's the schematic for that database,

04:15

and right here we can see our department's table,

04:19

our department imp pivot tables right here

04:24

and our employees table. And again we have the one and only one too many

04:30

and the one and only one too many.

04:32

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

04:46

on this table as it occurs. Multiple times sales.

04:48

The department idea one is mission twice,

04:53

so that is the the many too many relationships.