1
00:00:00,540 --> 00:00:04,490
So we've established that in real life, data can be crazy complex.

2
00:00:04,500 --> 00:00:07,680
Outside of the classroom, things aren't as nice as we'd like them to be.

3
00:00:08,250 --> 00:00:09,660
So we've established that fact.

4
00:00:09,660 --> 00:00:16,680
But now where do we start to kind of wrap our heads around how we represent complex data using MySQL?

5
00:00:16,680 --> 00:00:21,270
And the answer is we're going to start by talking about the relationship basics.

6
00:00:21,360 --> 00:00:27,630
So I know it sounds like Marriage Counseling 101 or something, but this is not it's not that this is

7
00:00:27,630 --> 00:00:31,380
about the different ways that data can be related.

8
00:00:31,500 --> 00:00:37,740
So if we have two tables or two entities, things like orders and customers and students and teachers,

9
00:00:38,370 --> 00:00:42,420
movies and reviews and users, how, how are they related?

10
00:00:42,420 --> 00:00:43,800
And there's a couple of different ways.

11
00:00:43,800 --> 00:00:48,210
There's kind of these big, broad categories we can break relationships down into.

12
00:00:48,420 --> 00:00:52,830
And the terms that I'm going to use that you'll occasionally come across are three.

13
00:00:52,830 --> 00:00:57,330
There's the first one, which is a 1 to 1 relationship, and I'm just going to go through all of them

14
00:00:57,330 --> 00:01:00,600
first before we talk about what they mean and talk about examples.

15
00:01:00,600 --> 00:01:08,520
So 1 to 1 relationship, a one to many relationship, and then a many to many relationship.

16
00:01:08,730 --> 00:01:13,050
So the reality is that 1 to 1 relationships aren't actually that common.

17
00:01:13,680 --> 00:01:21,270
But perhaps another example or one that I've recently used is had a customers table that had the basic

18
00:01:21,270 --> 00:01:22,620
information for a customer.

19
00:01:22,620 --> 00:01:28,380
So things like their username and their password, although again you don't actually store the password

20
00:01:28,380 --> 00:01:32,280
directly, but username and password and email and registration date.

21
00:01:32,280 --> 00:01:36,900
And then there was a separate table where we stored all these details about our customer.

22
00:01:36,900 --> 00:01:42,630
So rather than storing ten or 20 extra columns on the basic customer table, we decided to split it

23
00:01:42,630 --> 00:01:46,830
up based off of what was most commonly needed in the app we were building.

24
00:01:46,830 --> 00:01:52,440
And most commonly all we needed was the username and the password and the email, but there was a whole

25
00:01:52,440 --> 00:01:54,240
bunch of other information that we were storing.

26
00:01:54,240 --> 00:01:58,470
So we created a customer details table and that's a 1 to 1 relationship.

27
00:01:58,470 --> 00:02:06,540
One customer has their own row, one row in the customer details table, and one customer details row

28
00:02:06,570 --> 00:02:08,880
is associated with one customer.

29
00:02:09,210 --> 00:02:17,520
So cold steel has a cold steel customer details, but there's no other user or customer that could be

30
00:02:17,520 --> 00:02:20,460
associated with it because it's a 1 to 1 relationship.

31
00:02:21,060 --> 00:02:23,280
Anyways, like I said, it's not the most common.

32
00:02:23,280 --> 00:02:28,800
What is the most common is actually a one to many relationship, and that's what we'll spend the most

33
00:02:28,800 --> 00:02:32,160
time on in this course because like I said, it's really common.

34
00:02:32,160 --> 00:02:33,360
So here's an example.

35
00:02:33,360 --> 00:02:36,180
The relationship between books and reviews.

36
00:02:36,510 --> 00:02:39,930
One book can have thousands of reviews, right?

37
00:02:40,620 --> 00:02:46,530
Let's say Into Thin Air by Jon Krakauer, could have probably tens of thousands of reviews by this point.

38
00:02:46,530 --> 00:02:47,490
It's been out for a while.

39
00:02:47,490 --> 00:02:53,910
It's pretty popular, but those reviews belong to that book exclusively to one book.

40
00:02:54,560 --> 00:02:59,150
So the relationship is books have many reviews.

41
00:02:59,930 --> 00:03:02,990
But reviews belong to one book.

42
00:03:03,620 --> 00:03:05,150
So it's a one to many.

43
00:03:05,150 --> 00:03:07,520
So that's a really common classic example.

44
00:03:08,000 --> 00:03:11,690
Reviews or ratings and books or some entity.

45
00:03:12,640 --> 00:03:12,930
Okay.

46
00:03:13,100 --> 00:03:20,570
So then the next thing that we'll discuss here is a many to many relationship and many to many is still

47
00:03:20,570 --> 00:03:23,450
relatively common and the way that it works.

48
00:03:23,780 --> 00:03:30,500
So what it sounds like we have two entities in this case books and authors, and you can kind of speak

49
00:03:30,500 --> 00:03:30,770
through it.

50
00:03:30,770 --> 00:03:33,470
You could say, okay, books can have many authors.

51
00:03:33,500 --> 00:03:34,460
That's true.

52
00:03:34,490 --> 00:03:39,620
We could have two authors writing a book together, and those authors can have many books.

53
00:03:39,800 --> 00:03:43,460
There's no rule that says authors can only write one book.

54
00:03:44,010 --> 00:03:48,710
You know, J.K. Rowling, Rowling have already say it has ten plus books.

55
00:03:48,710 --> 00:03:51,800
So it's a two way, a many to many relationship.

56
00:03:51,800 --> 00:03:53,480
So hopefully you can see the difference there.

57
00:03:54,020 --> 00:03:57,710
Reviews in books, reviews belong to one book.

58
00:03:57,710 --> 00:03:59,660
They're associated only with one book.

59
00:03:59,660 --> 00:04:06,200
Whatever the book title is for that review, it can't also be the same review associated for another

60
00:04:06,200 --> 00:04:06,740
book.

61
00:04:06,740 --> 00:04:09,650
So that part of the relationship is one, right?

62
00:04:09,650 --> 00:04:13,400
And then we have the books to reviews, which is a many relationship.

63
00:04:13,400 --> 00:04:14,630
Books have many reviews.

64
00:04:15,230 --> 00:04:21,589
This is different because books can have multiple authors and authors can belong to multiple books or

65
00:04:21,589 --> 00:04:23,480
authors can have multiple books.

66
00:04:23,480 --> 00:04:27,170
And the way that we represent this is actually a little more complex.

67
00:04:27,170 --> 00:04:28,940
So we're going to come back to it.

68
00:04:29,060 --> 00:04:33,770
We're going to start off in this section by talking about the most common relationship and how we represent

69
00:04:33,770 --> 00:04:35,900
that, which is the one to many relationship.

70
00:04:35,900 --> 00:04:36,920
So that's coming up next.