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, 45:12
3.Managing Pages6 lessons, 1:12:31
4.User Management2 lessons, 27:40
5.Managing the Blog3 lessons, 42:25
6.Adding Extras2 lessons, 25:48
7.Implementing the Front-End3 lessons, 32:10
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, and 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 mambo jumbo. All of our database oriented code is actually written in PHP. And we have two 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, it 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 is 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 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 also unique. So this is an index that is built for this table, and it makes sure that every record has a unique email. And then there is a password, the remember token, and then the time stamps. The time stamps 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 field 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 could see that it reverses the up method. It simply drops the users 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 phpMyAdmi, But you might not have that and if you are using homestead than workbench would be a better tool for you. It is freely available, you just go to MySQL website and download and install workbench and you're good to go. You of course, to need to set up a data base connection, but there's nothing new there. It's all 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 Laravel CMS and we're going to Apply, and then Apply again, and Finish and then 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, nd then the password resets table. And actually it's going to create another one called migrations. So we'll go to the command line to run our migrations, we used php artisan migrate. And it's going to look at the files inside of database migrations and let'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, it's not going to run the migrations as we have already run, is 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're 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. Let'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 then also keeps track of which batch that those migrations were running. 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 content, or at least the 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 pages table, and whenever we press Enter it's going to create that migration. So, we can look inside of a our migrations folder, we now have a third file and the name of the class is CreatePagesTable. So it has taken whatever name we specified and made that into the class name. It extends migration, it has given us some boiler plate code. Now notice that it is creating a 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 sometime 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, --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 file name 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 can do that as well. Now, there's a third was that you can create a migration. And that is whenever you create a modal, because you are going to work with our contents or our pages instead of a code and it makes sense to go ahead and create a modal class for that. So we can not only create a modal class, but we can create the migration for that model class as well. So that is php artisan make:model, and we're going to call this simply Page because our model class is 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 creatPagesTable class and we have the code that gives us the bare essentials, our id field, and our time stamps. 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 url is fine as well. And this needs to be unique because, it's kind of like the email within the user's table. The URL need 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 is an integer. And we will call this user_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 online, let's say, php artisan migrate. Now, let's first of all do this, let's say, migrate, and then status. We're going to see the status of our migration, 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 rollback. 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 these pages table, and if we go back and run the status. We're going to see, that all three of our migrations had been run, let's also look at the contents of the migrations table, but we have the right here. So let's run this query again, we now have the 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 rollback 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 rollback again, that's going to rollback the users, and the password_resets table. So once again, if we refresh we are 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 we 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 user's table and the password_resets table, well, they were still batch 1. But now I'll create pages table as batch 1. 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 would do that with a method simply called pages. And we are going to return this, this is going to be a hasMany relationship, because a user is going to have many pages, and then we just say App\Page. But then we also need to setup the reverse or the inverse 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. And so now that we have 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.