2.2 Designing the Page Schema
The first thing we need to do is design our database schema. In this lesson, we'll focus on how to store individual pages in the database.
1.Introduction1 lesson, 01:23
2.Getting Started4 lessons, 46:41
3.Managing Pages6 lessons, 1:12:31
4.User Management2 lessons, 27:37
5.Managing the Blog4 lessons, 41:51
6.Adding Extras2 lessons, 26:07
7.Implementing the Front-End3 lessons, 30:24
8.Homework Review1 lesson, 07:11
9.Conclusion1 lesson, 01:24
2.2 Designing the Page Schema
In this lesson, we are going to focus on our database schema. Because we have a CMS and that is extremely data oriented we need to spend some time thinking about how we want to organize our data. And we essentially have three things that we need to keep track of. The users, they need to be able to log in, and create an edit content. They also need to have some permissions because not every user needs to edit every post. And not every user needs to manage other users. So there's going to be a hierarchy there of permissions. So we need to keep track of that. And we of course, also need to keep track of the content. And we need to be able to link content with the user that created that content. So there's several things that are going to be going on. And in this lesson, we're going to focus on two of those, the users and the content. So when it comes to working with our database schema, we use what are called migrations. A migration is just a way of versioning your database, and it's not something that is specific to Laravel or PHP. It is something that is used industry wide, or at least the concept of migrations are used industry wide. Of course, the implementation here in Laravel is very specific to PHP and Laravel. So the great thing about migrations is that we don't have to worry about any SQL mumbo jumbo. All of our database oriented code is actually written in PHP. And we have to migrations that are created for us. Whenever you create a project, there is a migration for creating the users table and then a migration for creating the password resets table. These two files are inside of the database migrations folder, and you can see what they are here. And a migration is nothing more than a class, iIt extends the migration class. And a migration has two methods. The first is called up, this is the code that it's going to execute whenever you run the migration. So whenever you create a database table, or you update, or edit that table schema, not the data itself but the schema or the structure of that table. That is what the up method is for. And then, there is a down method that essentially reverses whatever the up method did. So, we can see here, for the create users table migration, inside of the up method, it's using the schema builder. It creates a user's table, and it defines the fields within that table. The first is an id, it's an auto incrementing field. So that's every record has a unique id. And I am of the philosophy that every table should have an id, even if you don't think your table needs one, it needs one. So there is the id field. Then there is a field for the name, it is a string. There is an email that is a string, but it is also unique. So this is an index that has built for this table. And it makes sure that every record has a unique email. And then there's a password, the remember token, and then the timestamps. The timestamps method actually creates two fields. One is called created at, the other is called updated at. And those are very useful not just in our application, but any application. It's a tracking fields, so that you can keep track of when a record was created and when it was last updated. So that is the up method of this migration. If we look at the down method, we can see that it reverses the up method it simply drops the user's table if it exists. So the first thing we need to do before we run these migrations is create our database. And I'm going to use MySQL Workbench. Now, I typically use PHP myadmin but you might not have that. And if you're using Homestead then Workbench would be a better tool for you. It is freely available, you just go to MySQL's website and download and install Workbench and you're good to go. You of course, need to set up a database connection. But there's nothing new there, It's all of the same information that we input in the previous lesson, so there you'll go. And we just want to create a new database, they call it a new schema. So we're going to call this Laracms, and we're going to apply and then apply again and finish and we have a database. If we look at that database, we don't have any tables. So whenever we run this migration, we are going to end up with two tables. The first is going to be the Create users or I'm sorry, the users table. And then the password resets table. And actually, it's going to create another one called migrations. So we go to the command line to run our migrations, we use PHP, artisan, migrate. And it's going to look at the files inside of database, migrations, and it's going to run them, but it also keeps track of which migrations have been run. So even though we're going to say php artisan migrate, and we're going to migrate that whenever we create another migration Is not going to run the migrations that we have already run. It's going to run all of the new ones. So we're going to actually see an error here, and you might not see this error. If you don't, then great, you don't have to worry about anything. The reason why we see this error is, because I'm using an older version of MySQL, which you might be as well, or your production environment might be using an older version of MySQL. And you can see that the max key length is 767, this is actually easy to fix. We just have to go to the app folder, and then providers, and then go to the app service provider. And we need to add a use statement for illuminate support facades, schema. And then inside of the boot method, we need to call schema, and default string length 191 >> Now once again, you don't need to do this. If you did not get this error here, this is only if you got the error. So with that, we need to go to MySQL, and we need to refresh this because even though we got those errors. It still created this migrations table, it also created the users table. SO we are going to drop those tables, and then we are going to run the migrations again. So let's go back to the command line, let's php artisan migrate and there we go, it created those tables. So now, if we refresh our database once again, we're going to have the migrations table that's inspect this. And actually no, we want to select those, and we can see the migrations that it has run. So if we select all of the rows from migrations, we can see that it ran the migration for the Create users table. It also ran the migration for the create password resets table. And it also keeps track of which batch that those migrations were ran in. So we ran the migrations once that is the first batch of migrations. Whenever we create other migrations and run those, then those will be the second batch. And then the next ones will be the third batch and so on and so forth. Well, let's create another migration, one that is going to be for our contents, or at least a table that's going to store our content. And we can do this in a few different ways. The first way is to say php artisan, make migration, and then we give this migration a name. So it would be creating a table, let's just call this table pages. So create the pages table. And whenever we press enter, it's going to create that migration. So we can look inside of our migrations folder. We now have a third file, and the name of the class is create pages table. So it has taken whatever name we specified, and made that into the class name. It extends migration. It has given us some boilerplate code. Now notice that it is creating the table called pages. So it was smart enough to pick out the name of the table that we wanted to create, and that's very nice. Now, sometimes we might not want that, we might want to actually specify the name of the table. So let's delete this file. Now whenever you create a migration, all you have done is essentially created a PHP file inside of the migrations folder. It has not updated the database that only occurs whenever you run the migration. So, creating a migration, no harm, no foul, it's just a file on the file system. You can delete that and start over if you want to, which is what we are going to do. We're going to call the same command, but instead we're going to say dash dash create. And then whatever name we want the table to be. So let's just say content. So we run this command, it's going to create another migration file. The filename is the same. The class name is the same, but look inside of the up method. It is now creating a table called content. So if you wanted to explicitly say what table that you wanted to create, you could do that as well. Now there's a third way that you can create a migration That is whenever you create a model. Because we are going to be working with our contents or our pages inside of our code. And it makes sense to go ahead and create a model class for that. So we can not only create the model class but also create the migration for that model class as well. So that is php artisan make model, and we're going to call this assembly page. Because our model classes typically the singular word of whatever we have used within the database, which is going to be pages. And then we can say --migration. Or we can just say -m, and that will do the same thing. So we can see that the model was created successfully. And you can see the name of the file create pages table. So once again, we have that create pages table class and we have the code That gives us the bare essentials, our id field and our timestamps. So let's go ahead and fill this out. So we have the ID, which is something that we definitely need. Now every page is going to have a title, so let's have a title field. Let's also have the URL of that page. So we can call this URI or URL. URI is probably the more correct thing to use, but URLs fine as well. And this needs to be unique, because it's kind of like the email within the users table. The URL needs to be unique for each record within this table. And then we need the actual content, so that is going to be a string as well. And we'll just call that content. But we also need to keep track of the user that created this. So we can say table, and that is going to be an integer, because the id field isn't integer. And we will call this user underscore id. And we can save that file. And that's really all that we need to do as far as our pages migration is concerned. So we can go to the command line. Let's say PHP artisan migrate. Now let's first of all do this, let's say well, let's say migrate and then status. We're going to see the status of our migrations. You can see that we have two migrations that were ran, but then our third one has not been run. So that is a nice little thing, and we can also roll back, but let's do this. Let's go ahead and migrate that's going to create that pages table. So if we look at workbench, let's refresh here, we have migrations, we have pages, password resets and users. So we now have this pages table. And if we go back and run the status, we're going to see that all three of our migrations have been run. Let's also look at the contents of the migrations table while we have that right here. So let's run this query again, we now have this third record. We can see that the batch was 2. Now the batch is going to change based upon whenever you run your migrations. We've run the migration command twice now. But let's do this, we can say, php artisan migrate rollback, that's going to roll back the last migration that was creating our pages table. So if we refresh our tables, we aren't going to see pages there. But if we roll back again, that's going to roll back the users and the password resets table. So once again, if we refresh, we're only going to see the migrations table. And whenever we look at the contents, there's nothing inside of this table now. But if we run migrate, then it's going to run all of our migrations because if we look at the status. You see that there are three migrations, none of which have been run. So if we run the migrate command, it's going to run all of those. And so if we look at the migrations table, we're going to then see that, the users table and the password resets table. While they were still batch one, but now create pages table is batch one. The only reason is because we ran the migration commands we had three migrations ready to run. So it ran all three of those in the same batch. So, batches going to change based upon whenever you run the migrations. So that is a very good starting point for our data. We have our users, we have our content in the form of our pages table. And we have the relationship between our users table and the pages table. And we could go ahead and set that up. So inside of our user class, what we want to do is say that there are the pages for this given user. And we will do that with a method simply called pages, and we are going to return this. This is going to be a has many relationship because a user is going to have many pages. And then we just say, APP Page, but then we also need to set up the reverse or the inverse of that relationship. That is from the page to the user. So here we will have public function user. And we will simply say this belongs to and then app user, and we are good to go there. So now that we have those two pieces done, we need to focus on our permissions. And how we are going to manage those within our database, and we will do that in the next lesson.