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

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

3
00:00:05,580 --> 00:00:07,950
who 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,050
So what I'm going to do here is something super simple.

10
00:00:34,140 --> 00:00:40,200
Like I said in the last video, there's a lot more we can store here email password, like 20 different

11
00:00:40,200 --> 00:00:44,850
things, location, city, IP address, all that stuff.

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

13
00:00:47,250 --> 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:15,660
For example, when we talk about photos, photos will have a foreign key referencing the user ID, so

17
00:01:15,660 --> 00:01:16,350
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:53,170
So id is going to be integer or just int I'll do integer just because I've been doing int, but it's

24
00:01:53,170 --> 00:01:54,340
an alias for integer.

25
00:01:54,430 --> 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,560 --> 00:02:03,160
Great.

28
00:02:03,670 --> 00:02:12,520
Comma, come back to username 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:20,020
It's easier to store.

32
00:02:20,940 --> 00:02:23,340
Okay, so then we've got username.

33
00:02:23,350 --> 00:02:26,820
We know it's just going to be a var cha 255.

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

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

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

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

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

39
00:02:45,990 --> 00:02:47,730
You absolutely could do that.

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

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

42
00:03:01,440 --> 00:03:05,090
that's going to be slower and more annoying to search potentially.

43
00:03:05,220 --> 00:03:10,470
Well, not potentially, definitely slower if you have thousands and thousands of entries compared to

44
00:03:10,470 --> 00:03:12,030
a smaller integer.

45
00:03:12,390 --> 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

46
00:03:18,390 --> 00:03:22,830
we can also add, not know, we don't want anyone to be able to sign up without a username.

47
00:03:24,330 --> 00:03:28,320
I'm going to go ahead and make a new database to put this in, but I'm going to do it in this file.

48
00:03:28,320 --> 00:03:36,720
So I'll do a create database and I'll also just call it Instagram or IG clone, and then I'll use IG

49
00:03:36,720 --> 00:03:37,830
clone just like that.

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

51
00:03:45,030 --> 00:03:46,260
and create the table.

52
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

53
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

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

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

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

57
00:04:10,230 --> 00:04:11,610
then create the table users.

58
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

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

60
00:04:25,200 --> 00:04:25,760
Okay.

61
00:04:25,830 --> 00:04:31,530
And just to double check, describe users, it looks good.

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

63
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

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

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

66
00:04:46,560 --> 00:04:48,960
But if we want, we can just play around with some data.

67
00:04:48,960 --> 00:04:52,650
So I'll do an insert into users.

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

69
00:04:59,370 --> 00:05:02,970
So we'll have blue the cat.

70
00:05:04,020 --> 00:05:13,860
And we could also have, I don't know, Charlie Brown and then I'll just put myself in there just like

71
00:05:13,860 --> 00:05:14,340
that.

72
00:05:14,990 --> 00:05:15,490
Okay.

73
00:05:16,230 --> 00:05:23,820
So what we could do now, if I just do if I resource this whole thing, I'll get an error because it

74
00:05:23,820 --> 00:05:25,260
tells me I can't create this database.

75
00:05:25,260 --> 00:05:25,920
Instagram clone.

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

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

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

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

80
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

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

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

83
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

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

85
00:05:56,700 --> 00:05:59,280
So now I can do a select star from users.

86
00:05:59,460 --> 00:06:00,350
And there we go.

87
00:06:00,360 --> 00:06:01,680
We've got created that in there.

88
00:06:01,680 --> 00:06:05,610
Automatically IDs automatic and our three usernames.

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