1
00:00:02,510 --> 00:00:04,780
So we're going to build a blog.

2
00:00:04,780 --> 00:00:09,270
And therefor I of course want to create a database

3
00:00:09,270 --> 00:00:14,270
that has a posts table that holds all the blog posts.

4
00:00:14,560 --> 00:00:17,090
Now, of course, there are dozens of ways

5
00:00:17,090 --> 00:00:20,940
of storing post data, but for me,

6
00:00:20,940 --> 00:00:24,160
a post that should be stored in the database

7
00:00:24,160 --> 00:00:26,550
should have some unique ID

8
00:00:26,550 --> 00:00:29,460
by which we can identify this post.

9
00:00:29,460 --> 00:00:31,050
This will also be important

10
00:00:31,050 --> 00:00:35,030
for fetching and displaying a single blog post,

11
00:00:35,030 --> 00:00:38,720
and for updating and deleting blog posts.

12
00:00:38,720 --> 00:00:41,990
We will need that unique ID for that.

13
00:00:41,990 --> 00:00:45,680
And then every blog post should have a title,

14
00:00:45,680 --> 00:00:49,670
which, well, is at the top of the blog post, I guess.

15
00:00:49,670 --> 00:00:52,900
Then a short summary excerpt which we show on

16
00:00:52,900 --> 00:00:57,450
the All blog posts page, where we can see all the blog posts

17
00:00:57,450 --> 00:01:00,460
and we see the short description.

18
00:01:00,460 --> 00:01:04,730
And then I wanna have a body, so the full blog post text,

19
00:01:04,730 --> 00:01:07,620
which is not visible on the overview page,

20
00:01:07,620 --> 00:01:09,450
but on the detail page,

21
00:01:09,450 --> 00:01:12,980
once we clicked on a single blog post.

22
00:01:12,980 --> 00:01:16,373
Then I wanna have the main body that will be displayed.

23
00:01:17,490 --> 00:01:21,670
Now, a blog post should also have a date.

24
00:01:21,670 --> 00:01:24,270
So that's simply a timestamp

25
00:01:24,270 --> 00:01:27,920
where the blog post was created.

26
00:01:27,920 --> 00:01:31,270
And then I also want to have information about

27
00:01:31,270 --> 00:01:33,703
the author of a blog post.

28
00:01:34,670 --> 00:01:35,830
For this we could, of course,

29
00:01:35,830 --> 00:01:39,480
just store the author name in my post here,

30
00:01:39,480 --> 00:01:42,550
but instead we're going to do it as we learned it

31
00:01:42,550 --> 00:01:44,340
in the previous course section,

32
00:01:44,340 --> 00:01:46,720
and we're going to normalize our data,

33
00:01:46,720 --> 00:01:51,000
and we're going to create a separate authors table,

34
00:01:51,000 --> 00:01:55,090
because here every author should also have a unique ID.

35
00:01:55,090 --> 00:01:57,920
Basically every table that you create

36
00:01:57,920 --> 00:02:00,730
has a unique ID for its records.

37
00:02:00,730 --> 00:02:03,900
But then I wanna have a name and an email address

38
00:02:03,900 --> 00:02:05,280
for the author.

39
00:02:05,280 --> 00:02:08,300
And that's why I wanna have a separate table for that,

40
00:02:08,300 --> 00:02:11,720
for one, so that we don't have to re-enter the name

41
00:02:11,720 --> 00:02:14,260
for every new blog post we create,

42
00:02:14,260 --> 00:02:17,440
because chances are high that we only have a few authors

43
00:02:17,440 --> 00:02:19,690
that write all our blog posts,

44
00:02:19,690 --> 00:02:23,190
and it would be very cumbersome and error prone

45
00:02:23,190 --> 00:02:27,340
if we have to re-enter their names for every new blog post.

46
00:02:27,340 --> 00:02:30,040
It would be easy to add typos

47
00:02:30,040 --> 00:02:32,220
or write the name slightly different,

48
00:02:32,220 --> 00:02:34,390
even though it's the same author.

49
00:02:34,390 --> 00:02:37,470
And in addition, since the author is not just

50
00:02:37,470 --> 00:02:40,150
defined by its name, but also by the email,

51
00:02:40,150 --> 00:02:44,360
we don't wanna squeeze all that data into the posts table.

52
00:02:44,360 --> 00:02:46,900
Instead, having a separate authors table

53
00:02:46,900 --> 00:02:49,120
which we connect to the posts table

54
00:02:49,120 --> 00:02:50,960
is the much better approach,

55
00:02:50,960 --> 00:02:53,330
and how we typically should structure our data

56
00:02:53,330 --> 00:02:55,143
when working with SQL.

57
00:02:56,410 --> 00:02:59,500
Well, and then of course, to establish this connection,

58
00:02:59,500 --> 00:03:02,250
every post should have an authorID,

59
00:03:02,250 --> 00:03:05,820
which links to the ID of an author.

60
00:03:05,820 --> 00:03:10,023
So to the ID of a single record in that author's table.

61
00:03:11,480 --> 00:03:14,850
Now, of course, the different columns which we will have

62
00:03:14,850 --> 00:03:17,770
also should have different data types.

63
00:03:17,770 --> 00:03:19,620
And for the ID, for example,

64
00:03:19,620 --> 00:03:22,970
it makes sense to use the integer data type,

65
00:03:22,970 --> 00:03:26,680
both for posts and the author, and the author of course,

66
00:03:26,680 --> 00:03:29,080
for authorID, we also have integer,

67
00:03:29,080 --> 00:03:32,040
because that always has to be the same data type

68
00:03:32,040 --> 00:03:35,763
as for the primary key in the table we're linking to.

69
00:03:36,850 --> 00:03:39,880
And then of course, we have more columns here,

70
00:03:39,880 --> 00:03:41,990
and for the title and the summary,

71
00:03:41,990 --> 00:03:44,400
it makes sense that we use VARCHAR,

72
00:03:44,400 --> 00:03:46,593
so a string with a maximum length.

73
00:03:47,560 --> 00:03:50,380
And then for the body, we can use the TEXT data type

74
00:03:50,380 --> 00:03:53,700
since that could theoretically be a very long text

75
00:03:53,700 --> 00:03:56,363
and we don't wanna impose any limits there.

76
00:03:57,460 --> 00:04:01,010
For the date we can use the DATETIME data type of course,

77
00:04:01,010 --> 00:04:03,610
to store both the date and information about

78
00:04:03,610 --> 00:04:06,410
the exact time when this was inserted.

79
00:04:06,410 --> 00:04:08,190
And I wanna store it such that

80
00:04:08,190 --> 00:04:10,520
it's automatically set to the time

81
00:04:10,520 --> 00:04:13,410
when that post is inserted, so that we don't have to

82
00:04:13,410 --> 00:04:16,440
manually determine that's time.

83
00:04:16,440 --> 00:04:19,779
And forward the authors, their name and email again

84
00:04:19,779 --> 00:04:23,800
can be strings because this shouldn't be too long of a text.

85
00:04:23,800 --> 00:04:25,940
You won't have a maximum length there.

86
00:04:25,940 --> 00:04:30,100
And hence these are probably value types that make

87
00:04:30,100 --> 00:04:33,193
a lot of sense for the kind of values we're storing here.

88
00:04:34,180 --> 00:04:36,450
Now, of course, this is just one way of

89
00:04:36,450 --> 00:04:38,510
structuring and storing this data.

90
00:04:38,510 --> 00:04:41,550
As I mentioned before, there would be many other ways of

91
00:04:41,550 --> 00:04:43,850
defining a post and authors,

92
00:04:43,850 --> 00:04:47,930
but this is fairly simple yet still we have a connection.

93
00:04:47,930 --> 00:04:49,680
We will be able to join data and therefor

94
00:04:49,680 --> 00:04:53,020
this should be great for practicing what we learned

95
00:04:53,020 --> 00:04:54,780
and for applying what we learned

96
00:04:54,780 --> 00:04:57,313
in our NodeJS driven website.