Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this movie, we're going to set up the database tables that we'll need for our content management system. In addition to blueprinting the whole project, it's also very helpful to map out what our project's database tables are going to look like. This phase is called data modeling because we're thinking about what kind of data we'll have and how we'll store it and how it relates to each other. In our data model, we're going to have three tables. We're going to have subjects, we're going to have pages, then we're going to have our admins. So, for subjects, you can see that we have our ID. That's the primary key, the unique value for finding any subject.
Then we'll have it's menu_name, the common name that the user will see, it's position, so that we can sort them in a certain order. And then a visible Boolean, that determines whether or not the subject is visible to the public. Then we our pages, which are almost exactly the same, except that they have a subject_id, which is a foreign key that relates it to subject. So subject has many pages and that subject_id is how we link those together. We talked about that when we talked about relational databases. And then pages also has a big block of content in it which is the page content. That's the content that's going to be up there that'll have paragraphs of text for the user to read.
And then our 3rd table is going to be admins which is going to have it's primary key, once again being id. And then the username that the admin would use to log in with and then hashed_password. And you could just call this password, but I'm calling it hashed_password just to make it clear that it's going to be encrypted. And we'll talk about that when we actually get to the section on user authentication. Now, you might want to scribble in more details than this. You might want to write in the type of SQL column that you're going to need for each one, or details about the relationships between the tables. Or maybe reminders to yourself about adding indexes on foreign keys.
The overall idea is just to have it all mapped out in front of you, just like you did for blueprinting the application. Because now, once again, it's all in front of us. We can examine it, we're not trying to hold it in our head. We can confront problems early on, figure out the solutions before we start developing. Now, we already created both the subjects table and the pages table back in chapter 13. If you don't have them already, you'll want to either jump back there to learn how, or load the SQL that's included with the exercise files, so that you'll have them. What we don't have yet is our admins table.
Now, it's a pretty simple table, and in our simple model, it doesn't have a relationship to the subjects or the pages. It's just a standalone table that we're going to use to decide if someone should be allowed access to our admin area. And you could create a fancier version of this CMS where an admin was the owner of the subject or page. Or you could keep track of which users last edited a page in a new table called Activity Log. We're just going to create a simple CMS though, so you get the key concepts. But you can certainly build features on top of our foundation once you get the hang of it. Let's go to my SQL now and add the Admins Table.
So from the command line, I'm going to go into MySQL. I'm going to go in with my user name widget_cms. You can also go in as root. If you want to use your root password that's fine, but our widget_cms user ought to have enough privileges to do what we want to do. And then dash p to say I'm going to provide the password, and then the name of the database I want it to drop me straight into. It's going to ask for the password for me that was just secret password. You might have something different, that's fine, now here I am inside my SQL, I'm just going to clear the screen so that it's back up here at the top. So let's create our admins table.
CREATE TABLE admins, and make sure that's plural, and then I'm going to open my parenthesis. Now, in the next time, I'm just going to indent a couple spaces, I'm going to put in my id. That's going to be like the other ids that we created, integer of size 11, NOT NULL, and AUTO_INCREMENT. Now, you don't have to do all capitals like this, I'm just doing it because I think it makes it clearer and easier to read, it's fine if you do it in lower case. It works just as well. Then I'll put a comma at the end, return, space space, username, that's going to be a VARCHAR field, remember, that's just a string.
It can be any length we want, but I'm going to limit it to 50. There's no reason we should have usernames longer than 50 characters. And then NOT NULL comma, and then we're going to do our hashed_password. That's going to be an encrypted password, and that's also going to be a string. And I'm going to make it of length 60. And there's a specific reason why I'm doing 60 so go ahead and make sure you do that as well. We'll talk about that when we get to incrementing. Not NULL and then last of all, primary key and then id is the primary key.
Okay, everything looks good so I'll just close my parenthesis from the top, put a semicolon at the end and now I've created my admins table. Show tables and there we are, show fields from admins, and there's what my table looks like. So I've now created my admins table, I have pages, I have subjects and I have admins. Those are the three tables that I'm going to need. Now, I do recommend that you try and create as many of your tables as you can right at the start of your project. If the data or modeling reveals any problems, you'll want to address those before you start, and it may change your approach to designing the pages. For example, our data model assumes that a page can only show up under one subject, and that's correct, that's exactly how we want it. But, if we discovered that the same page of content could appear under multiple subjects, then we might need to rethink our page navigation choices. So, to recap, in order, draw a blueprint for the whole project, then write up your data model.
And then create your database tables. At that point, you'll be ready to start developing.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 99921 Viewers
56 Video lessons · 113024 Viewers
71 Video lessons · 81864 Viewers
131 Video lessons · 39284 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.