1
00:00:00,150 --> 00:00:00,660
All right.

2
00:00:00,660 --> 00:00:05,610
So this is the first video of basically talking about the tables in need and implementing the tables

3
00:00:05,610 --> 00:00:07,950
we need for our Instagram clone.

4
00:00:08,039 --> 00:00:10,710
And the first thing we'll start with is users.

5
00:00:10,830 --> 00:00:13,800
Users is a pretty obvious place to start.

6
00:00:14,220 --> 00:00:18,780
I guess another one might be photos, but users is at the core of everything, if you think about it,

7
00:00:18,780 --> 00:00:24,390
because users are connected to, you know, photos, are connected to comments, they're connected to

8
00:00:24,390 --> 00:00:30,480
likes, they're not connected to hashtags, but pretty much everything else relates to users.

9
00:00:30,900 --> 00:00:34,110
So what I'm going to do here is something super simple.

10
00:00:34,140 --> 00:00:37,590
Like I said in the last video, there's a lot more we can store here.

11
00:00:37,590 --> 00:00:44,850
Email, password, like 20 different things, location, city, IP, address, all that stuff.

12
00:00:45,030 --> 00:00:47,220
We're just going to focus on the basics.

13
00:00:47,220 --> 00:00:55,020
So an ID, a username and a created it and created that will just be the day the user or the timestamp

14
00:00:55,020 --> 00:01:01,020
when the user signs up, it will have a default value of now or current timestamp username is just going

15
00:01:01,020 --> 00:01:08,970
to be a var char var car and id will be a primary key and we'll be referencing this ID in other places.

16
00:01:08,970 --> 00:01:14,790
For example, when we talk about photos, photos will have a foreign key referencing the user ID.

17
00:01:15,360 --> 00:01:16,350
So we'll go ahead.

18
00:01:16,350 --> 00:01:21,360
And the first thing I have is just an empty directory I made just to store this and I'm just going to

19
00:01:21,360 --> 00:01:27,300
make a new file and I'll just call it Instagram, underscore and clone, underscore, clone dot SQL.

20
00:01:28,080 --> 00:01:31,110
I'll open that up and this is where I'll work.

21
00:01:31,110 --> 00:01:35,070
So this is where I'll do a create table users.

22
00:01:36,150 --> 00:01:43,170
And like we said, we're going to have an ID, we'll have a username and a created ADD.

23
00:01:43,900 --> 00:01:52,480
So ID is going to be integer or just int I'll do integer just because I've been doing int.

24
00:01:52,480 --> 00:01:54,340
But it's an alias for integer.

25
00:01:54,440 --> 00:01:55,510
It's good to see both.

26
00:01:55,960 --> 00:02:01,930
And we'll have it auto increment and it's a primary key.

27
00:02:02,590 --> 00:02:03,160
Great.

28
00:02:03,670 --> 00:02:12,520
Comma, come back to your and just a second created that will do time stamp with a default of now.

29
00:02:13,330 --> 00:02:17,200
And you can also do current timestamp and remember you could do date time as well.

30
00:02:17,230 --> 00:02:18,850
Time stamp is just smaller.

31
00:02:18,850 --> 00:02:19,990
It's easier to store.

32
00:02:20,940 --> 00:02:21,510
Okay.

33
00:02:21,660 --> 00:02:23,340
So then we've got username.

34
00:02:23,360 --> 00:02:26,820
We know it's just going to be a var cha two, five, five.

35
00:02:27,270 --> 00:02:32,370
There's another constraint that we could add though, which is we want username to be unique.

36
00:02:32,370 --> 00:02:36,930
We don't want anyone to be able to sign up with another with the same username essentially.

37
00:02:36,930 --> 00:02:38,700
So we can add unique.

38
00:02:39,360 --> 00:02:43,800
And you might be wondering, well then why would we have ID be the primary key?

39
00:02:43,800 --> 00:02:45,990
Why not make username primary key?

40
00:02:45,990 --> 00:02:47,670
You absolutely could do that.

41
00:02:47,760 --> 00:02:53,730
But if you're going to have a foreign key somewhere referencing username and you're working with a long

42
00:02:53,730 --> 00:03:01,920
string, if someone's username is something massive like that because we could do 255 characters, that's

43
00:03:01,920 --> 00:03:04,770
going to be slower and more annoying to search.

44
00:03:04,770 --> 00:03:05,120
Potentially?

45
00:03:05,220 --> 00:03:06,630
Well, not potentially.

46
00:03:06,840 --> 00:03:12,030
Definitely slower if you have thousands and thousands of entries compared to a smaller integer.

47
00:03:12,360 --> 00:03:18,390
So it's good to have a primary key, be an integer, but we can still have this unique constraint and

48
00:03:18,390 --> 00:03:19,740
we can also add not know.

49
00:03:19,770 --> 00:03:22,830
We don't want anyone to be able to sign up without a username.

50
00:03:23,630 --> 00:03:27,690
Okay, I'm going to go ahead and make a new database to put this in, but I'm going to do it in this

51
00:03:27,690 --> 00:03:28,320
file.

52
00:03:28,320 --> 00:03:37,020
So I'll do a create database and I'll also just call it Instagram or IG clone and then I'll use IG Clone

53
00:03:37,020 --> 00:03:37,830
just like that.

54
00:03:38,250 --> 00:03:44,550
So now every time I run this file, it's going to make this database, use Instagram, clone the new

55
00:03:44,550 --> 00:03:46,260
database and create the table.

56
00:03:46,260 --> 00:03:51,030
And the reason I'm doing that is just one to show you that you can we haven't really seen using these

57
00:03:51,030 --> 00:03:58,860
commands in a file, but also as we go, we may want to rerun this and recreate the user's table or

58
00:03:58,860 --> 00:04:02,880
another table if we realize we messed something up or we need to change something, we're not going

59
00:04:02,880 --> 00:04:04,170
to have any data in there yet.

60
00:04:04,170 --> 00:04:10,260
So it's super easy just to create the database again, drop it, then create it and then use it and

61
00:04:10,260 --> 00:04:11,610
then create the table users.

62
00:04:11,970 --> 00:04:12,140
Okay.

63
00:04:12,300 --> 00:04:22,560
So with that said, let's try this going to do source and I need to reference this directory slash Instagram

64
00:04:22,560 --> 00:04:24,030
clone SQL.

65
00:04:25,200 --> 00:04:25,600
Okay.

66
00:04:25,830 --> 00:04:30,270
And just to double check, describe users.

67
00:04:30,660 --> 00:04:31,530
That looks good.

68
00:04:32,190 --> 00:04:34,980
So if you are good with that, go ahead and move on.

69
00:04:35,220 --> 00:04:39,570
Next up, we're going to talk about photos, but I will spend a minute or two just adding one or two

70
00:04:39,570 --> 00:04:42,270
users so that we can work with them in this section.

71
00:04:42,270 --> 00:04:46,560
Remember, in the next section, we're going to have a massive data set that I'm going to give you.

72
00:04:46,560 --> 00:04:52,650
But if we want, we can just play around with some data, so I'll do an insert into users.

73
00:04:53,970 --> 00:04:59,370
And just insert username and let's just do two or three.

74
00:04:59,370 --> 00:05:11,010
So we'll have blue the cat and we could also have, I don't know, Charlie Brown and then I'll just

75
00:05:11,010 --> 00:05:14,340
put myself in there just like that.

76
00:05:14,940 --> 00:05:15,510
Okay.

77
00:05:16,170 --> 00:05:23,730
So what we could do now, if I just do, if I resource this whole thing, I'll get an error because

78
00:05:23,730 --> 00:05:25,920
it tells me I can't create this database Instagram clone.

79
00:05:25,920 --> 00:05:26,940
It already exists.

80
00:05:27,120 --> 00:05:34,050
So I could either drop the database first and I'm going to do that for now.

81
00:05:34,140 --> 00:05:36,450
You typically don't want to do that.

82
00:05:36,750 --> 00:05:39,300
You don't want to drop a database ever if you can avoid it.

83
00:05:39,300 --> 00:05:44,910
But if you're there's no data in there yet, or if the only data that will be in there is in the same

84
00:05:44,910 --> 00:05:47,250
file like we're doing here, just testing it out.

85
00:05:47,430 --> 00:05:50,430
This makes it easy because I can just run this one command.

86
00:05:50,820 --> 00:05:55,800
It will erase the database, recreate it, and get all the new tables that I need in there, as well

87
00:05:55,800 --> 00:05:56,610
as the new data.

88
00:05:56,700 --> 00:06:00,350
So now I can do a select star from users and there we go.

89
00:06:00,360 --> 00:06:05,610
We've got created that in there automatically IDs automatic and our three usernames.

90
00:06:06,600 --> 00:06:08,040
Next up photos.