WEBVTT 00:00.110 --> 00:05.900 So now that we have the database abstraction class, it's time that we define the schema, which is 00:05.900 --> 00:08.540 the tables and the table columns. 00:08.540 --> 00:15.080 In this project we are going to have three tables the users, the posts and comments. 00:15.080 --> 00:18.800 And they are going to interact with each other. 00:18.830 --> 00:22.940 Previously we had only one database table. 00:23.000 --> 00:31.490 So by creating multiple tables we are going to learn some new concepts like the foreign keys, the concept 00:31.490 --> 00:37.070 of an owning side, and the concept of a one to many relationship. 00:37.070 --> 00:42.920 And now I'd like us to jump to the code editor and define those tables using SQL. 00:42.950 --> 00:49.520 And along the way, while we type, I am going to talk about all those concepts. 00:50.480 --> 00:57.650 So by the side I'm going to have a preview of SQL syntax that lets us create tables. 00:57.770 --> 01:00.680 We did that before in the previous project. 01:00.680 --> 01:05.330 The difference now is that we're going to introduce a new thing which is a foreign key. 01:05.360 --> 01:08.660 and we are going to define multiple tables. 01:08.840 --> 01:14.720 So let me add a schema SQL file inside the database folder. 01:14.720 --> 01:17.120 We did it the same way previously. 01:17.120 --> 01:20.450 This is where we keep the database structure. 01:20.480 --> 01:27.110 Now I'm going to enable Capslock as the convention is to write SQL using uppercase. 01:27.110 --> 01:29.210 Now lowercase is also fine. 01:29.210 --> 01:30.800 That's just a convention. 01:30.800 --> 01:34.310 So to create a table you can write create table. 01:34.310 --> 01:41.390 If not exists because of the not exists part, you can safely run the same query a couple of times, 01:41.390 --> 01:46.220 and it will only try to create the table if it doesn't exist yet. 01:46.250 --> 01:48.410 Now let me disable Capslock. 01:48.440 --> 01:50.330 That's the users table. 01:50.360 --> 01:57.200 Now you can see that I'm just using this syntax that's visually represented here, nothing else. 01:57.500 --> 02:00.290 So I'm going to define three tables. 02:00.290 --> 02:02.150 First that's users. 02:02.150 --> 02:05.810 That's posts and that's comments. 02:06.530 --> 02:09.960 Then we're going to add columns for every single one of them. 02:09.960 --> 02:14.730 And finally, I'm going to talk about the foreign keys and relationships. 02:15.990 --> 02:21.810 So first the users first column is ID, then again enabling capslock. 02:21.810 --> 02:25.890 And this is an integer that's a primary key. 02:25.890 --> 02:29.010 So unique identifier for every record. 02:29.010 --> 02:31.620 And it is an autoincrement. 02:31.620 --> 02:36.840 Which means we'd like this to auto increment every single time. 02:36.840 --> 02:39.180 We won't be setting this column manually. 02:39.180 --> 02:42.180 It's always set by the database. 02:43.020 --> 02:46.920 We'd like such column in every single table. 02:46.920 --> 02:49.500 That's why I'm just going to copy and paste that. 02:50.490 --> 02:53.520 So every single table needs a primary key. 02:54.690 --> 02:57.240 Next up some unique columns for the users. 02:57.240 --> 02:59.280 Every user should have a name. 02:59.280 --> 03:02.850 This would be text, not null. 03:02.880 --> 03:05.670 Then every user needs an email. 03:06.420 --> 03:10.410 This is also text but it's unique. 03:10.770 --> 03:20.960 So there can't be two users with the same email as email would be used as the login, then the password. 03:21.740 --> 03:24.680 This is also text, not null. 03:27.110 --> 03:33.560 Now the password will be hashed, but this is done on the PHP level, not on the database level. 03:33.590 --> 03:36.230 Another thing we're going to have is role. 03:36.260 --> 03:40.460 So we're going to have something called role based access control. 03:40.490 --> 03:48.890 This means you can have a role of a user admin etc. and your permissions will be decided based on your 03:48.890 --> 03:49.700 role. 03:49.730 --> 03:53.570 This is not new and needs a default. 03:54.050 --> 03:56.060 Default is just user. 03:56.060 --> 04:00.680 That's someone who can comment and then created ad. 04:00.710 --> 04:07.430 That's a date time where the default is the current timestamp. 04:07.430 --> 04:10.880 It means we will never manually change this column. 04:10.880 --> 04:14.840 It will be always populated by the database using the current time. 04:15.950 --> 04:21.620 So some of the columns can be reused across different tables like Createdat. 04:21.800 --> 04:28.790 It's really useful to have timestamps at your tables so that every single time a row is inserted, we 04:28.790 --> 04:32.030 just know automatically when this happened. 04:32.060 --> 04:38.750 It's very useful for debugging to figure out if and why something went wrong. 04:39.440 --> 04:44.600 So next up I'm going to add the basic missing columns to posts and comments. 04:44.600 --> 04:49.040 And then we're going to be talking about the foreign keys and relationships. 04:49.310 --> 04:52.940 So this here the posts needs a title. 04:52.940 --> 04:57.650 Every post needs to have a title and it can't be null. 04:57.650 --> 04:59.210 It also needs a content. 04:59.210 --> 05:05.360 So basically the content of the blog post, which also can't be null because it doesn't make sense. 05:05.360 --> 05:09.800 Otherwise we're going to be counting views of every single post. 05:09.860 --> 05:18.500 This would be an integer defaulting to zero, so it's also never null and then the comments what do 05:18.500 --> 05:19.400 we have here? 05:19.400 --> 05:26.680 Well, I think that there should be just Content, which is text and it's not null. 05:28.000 --> 05:32.680 And then we can jump to adding a foreign key. 05:33.250 --> 05:39.160 So the foreign keys they are references to a primary key of another table. 05:39.280 --> 05:47.380 And when strictly enforced, the foreign key can't point to a non-existing primary key in another table. 05:47.890 --> 05:50.050 So what does it mean in plain English? 05:50.380 --> 05:55.270 Examples always work best, so consider we have a post. 05:55.690 --> 05:58.960 The post should have a user ID column. 05:58.960 --> 06:03.250 That's why we can know who wrote that post. 06:03.280 --> 06:10.570 The user ID is a column on the posts table and it will be a foreign key. 06:10.600 --> 06:21.010 This means it will point to a user and to identify a user, it will point to a primary key from users. 06:21.310 --> 06:31.060 Essentially, this means that in this posts table we need to add a column that will be called user ID 06:31.540 --> 06:34.060 and it needs to be an integer. 06:34.090 --> 06:35.230 Why? 06:35.260 --> 06:39.130 Because it needs to be of the same type as the primary key. 06:39.160 --> 06:45.640 It points to and the id inside users, which is a primary key. 06:45.670 --> 06:48.670 As you can see, is of type integer. 06:49.240 --> 06:52.060 So it points to the user id. 06:52.240 --> 06:54.190 It can't be null. 06:54.460 --> 06:58.600 So that's the first step before we define the foreign key. 06:58.630 --> 07:00.040 That's just a column. 07:00.040 --> 07:01.750 It's not a foreign key yet. 07:01.780 --> 07:06.700 To add a foreign key you add that at the end of the table definition. 07:06.700 --> 07:16.000 By writing foreign key you point to the column or columns that are the foreign key. 07:16.030 --> 07:27.220 In our case, that's the user ID and it references the users table and specifically the ID column in 07:27.250 --> 07:28.240 that table. 07:28.240 --> 07:31.180 This is how you define a foreign key. 07:34.690 --> 07:42.880 So we definitely would like to have the same user ID inside comments, because comments are also written 07:42.880 --> 07:45.070 by a specific user. 07:45.100 --> 07:49.720 But what's more, every comment needs to belong to a post. 07:49.750 --> 07:58.000 That's why we add another foreign key, which is the post ID, and we need to define it separately. 07:59.230 --> 08:06.400 So another foreign key is for the post ID column, which references the posts table and inside it the 08:06.400 --> 08:07.480 ID column. 08:09.730 --> 08:13.660 This is generically called a one to many relationship. 08:14.500 --> 08:22.540 For example, that means that one record from the users table can be associated with many posts was 08:22.540 --> 08:25.270 one to many, so one to many. 08:25.270 --> 08:32.530 Relationship between user and posts, and also one to many relationship between post and comments. 08:32.650 --> 08:38.440 Additionally, you might hear a concept of an owning side. 08:38.560 --> 08:46.450 That's just an abstract concept, and it's often used to describe a table which has the foreign key. 08:46.510 --> 08:54.580 That might sound counterintuitive because you might think that user has many posts, but when you describe 08:54.580 --> 09:02.260 the owning side, you would say that post is an owning site because post contains a foreign key to the 09:02.260 --> 09:03.460 user's table. 09:04.540 --> 09:12.130 Okay, so we've got this concepts explained and visualized, and we've also got the SQL written. 09:12.130 --> 09:14.950 So just make sure you didn't make any mistakes. 09:14.980 --> 09:16.990 You need all those commas. 09:17.200 --> 09:18.670 It's super important. 09:18.670 --> 09:23.650 You need the semicolons at the end of every single declaration. 09:23.650 --> 09:25.840 Check out the code under this video. 09:25.840 --> 09:29.920 If you didn't make any mistakes, that's super crucial. 09:30.010 --> 09:34.000 And in the next video, we are going to run this schema. 09:34.000 --> 09:40.480 We're going to create a CLI script to load this into our SQLite database.