Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this movie we'll learn how relational databases work. The idea behind relational databases is simple but the results are very powerful. Let's imagine for a moment that a subject like about Widget Corp, that we created in the last movie. Let's imagine that it has a set of pages that should be listed under that subject. This is what would be called a one to many relationship. And when describing that relationship, you can say that a subject has many pages and that each page belongs to the subject. In order to understand why having a relationship is important, let's imagine for a moment that when each one of these pages displays on the final public site.
Then it also needs to display the subject name along with the page, the subject that it belongs to. So we could create a table for our pages and we could give that table a column called subject name. And then we could store the subject name with each and every page. But then what happens if the subject name changes at some point, we want to make a change to it? We'd have to change the name field in the subjects table and also change the subject name field for every single page in the pages table that has to do with that subject. It's much better and much more efficient if the page can just refer to the subject's table and get the data from its name field whenever it needs it.
Then if we want to make a change, we're just changing the data in one place: in the subject's table. And as we've seen with functions and loops and including files, not repeating yourself is almost always a good idea. It's better to write it once and then access it whenever it's needed. The same thing is true here. So now that we understand why we need these relationships. How do we actually go about creating relationships in MySQL? We're going to use a foreign key and you'll remember that before I gave you a definition of a foreign key was a table column whos values reference rose in another table.
So let's see how that works. Let's say we have our subjects table. We've seen that before. We have About Widget Corp. And then we have another table that we haven't created yet called pages. And it also has an ID, a menu name, position, and visible. Plus it has another column for content, which is the actual content that we're going to display to the user. That's the real meat of our content management system. Now, right now, these two tables don't have any relationship whatsoever. What I need to do is add a column to one of them where the values will reference rows in the other table.
And we're going to do that on a one to many relationship. We're going to do that on the many side, not on the one side but on the many side, we'll add a column. So I'm going to add a column between id and menu name called subject_id and it has a value of one that relates to a value of one that we have over in the subjects table. So now we have a relationship and that's because now if I have a page and I want to know what subject it belongs to. Let's say I want to find its menu name. Well then I can just ask my SQL to make a new query.
And query for the subject where ID equals one. How do I know that ID? Well it's stored in my subject ID field. It's right there. That's the one that I use. I ask you to make a new query, I get back the subject, I find its menu name, and I can display it on the page. And it works in reverse too. If I have a subject and I want to know all of the pages that belong to that subject. Let's say I want to generate a list of them, well, then I can ask my SQL generate a query and return to me all pages where the subject ID is the same ID as what I have in this case one. And I'll get back a list of all of those pages.
These now are relational database tables. Let's go back into my SQL and let's create this new pages table and relate it to our subjects table. Okay. So I'm in my SQL already and I've created my subjects table and populated it. Now, I'm going to create my pages table. So, I'm just going to clear my screen and we're going to do a new create tables statement. Create table pages and then in parenthesis I'm going to put those pairs of the name at the column followed by the definition. So just like before, we're going to us an ID of INT size 11, not null and auto_increment comma and make sure you spelled all that right especially increment.
Make sure you spell that correctly, hit Return and then the next one is going to be my foreign key. So that's subject_id, that's how I'm going to know it's related to subjects because subject, singular, id relates to subjects plural, the table. And it's going to be integer of 11, also not null and then menu name. That's going to also be a varchar of type 30, just like we did for the other one, not null. Position int 3, not null. And visible, it's going to be a tiny int 1, that will be my Boulian. And then for the next one, this is one that we didn't have before. This is going to be content.
That's the content of the page and that can be quite large. So varchar probably isn't what we want cause varchar typically is limited to 255 characters. It doesn't have to be. It can go much larger than that. But once you start getting over that size, you really should start thinking about having something else and text is what we want. That's the name of field, is text and that's an unlimited amount of text or at least close enough to unlimited that you don't need to worry about it. going to be a lot of text can go in that field. And then I'll hit Return. We're still going to have primary key defined just like we did before, id. But this time instead of just ending it, we're going to put a comma and I'm going to add one more here, which is index on subject ID. Now this is a foreign key already without me specifying. Were going to use it as a foreign key to this other table. Index is going to tell MySQL that it should also create an index for it, for fast look ups.
It automatically builds an index for us for the primary key. It always does that because it wants to be able to look up things quickly and the primary key's the main way that it does that. So it gives us that index for free but any other index we want to use we're going to need to add ourselves. So index on subject ID is going to tell it hey this is something you're going to be using often so go ahead and create an index for it. And you're always going to want to create indexes for your foreign keys. Okay and let's hit Return. We'll close our parenthesis and put a semicolon.
And now we'll create our pages table to show tables. So we can see it. And there we go. We see our pages table. And we can say show columns from pages. Now we can see what it looks like. And you'll see now that in addition to having our subject column here, it also tells us that we have a index on that. So now we have two tables in our database and we have a relationship between them. But we don't have any data that's actually related yet. The next movie, we'll see how to add that.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 99890 Viewers
56 Video lessons · 113005 Viewers
71 Video lessons · 81836 Viewers
131 Video lessons · 39273 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.