Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

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.
04:59
I hope you enjoy this lesson and I'll see you in the next lesson.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor