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

2
00:00:04,500 --> 00:00:07,680
Outside 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,660
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,390 --> 00:00:52,820
And the terms that I'm going to use that you'll occasionally come across are three.

13
00:00:52,830 --> 00:00:55,680
There's the first one, which is a 1 to 1 relationship.

14
00:00:55,830 --> 00:01:00,060
And I'm just going to go through all of them first before we talk about what they mean and talk about

15
00:01:00,060 --> 00:01:00,590
examples.

16
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.

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

18
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

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

20
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

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

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

23
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

24
00:01:42,630 --> 00:01:48,090
up based off of what was most commonly needed in the app we were building, and most commonly all we

25
00:01:48,090 --> 00:01:51,210
needed was the username and the password and the email.

26
00:01:51,390 --> 00:01:56,040
But there was a whole bunch of other information that we were storing, so we created a customer details

27
00:01:56,040 --> 00:01:58,470
table and that's a 1 to 1 relationship.

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

29
00:02:06,060 --> 00:02:08,880
Row is associated with one customer.

30
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

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

32
00:02:21,060 --> 00:02:21,540
Anyways.

33
00:02:21,540 --> 00:02:23,280
Like I said, it's not the most common.

34
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

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

36
00:02:32,160 --> 00:02:36,180
So here's an example the relationship between books and reviews.

37
00:02:36,510 --> 00:02:43,710
One book can have thousands of reviews, right, Let's say Into Thin Air by Jon Krakauer could have

38
00:02:43,710 --> 00:02:46,500
probably tens of thousands of reviews by this point.

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

40
00:02:47,490 --> 00:02:48,480
It's pretty popular.

41
00:02:48,780 --> 00:02:53,910
But those reviews belong to that book exclusively to one book.

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

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

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

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

46
00:03:07,970 --> 00:03:11,690
Reviews or ratings and books or some entity.

47
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

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

49
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

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

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

52
00:03:33,500 --> 00:03:34,440
That's true.

53
00:03:34,460 --> 00:03:36,920
We could have two authors writing a book together.

54
00:03:36,950 --> 00:03:39,650
And those authors can have many books.

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

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

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

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

59
00:03:54,020 --> 00:03:55,250
Reviews in books.

60
00:03:55,250 --> 00:03:57,710
Reviews belong to one book.

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

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

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

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

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

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

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

68
00:04:21,589 --> 00:04:23,450
authors can have multiple books.

69
00:04:23,450 --> 00:04:27,200
And the way that we represent this is actually a little more complex.

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

71
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

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

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