9.2 Create a Posts Table

In the previous lesson you were introduced to data bases and more specifically the mysecual data base. We used a web based tool called phpMyAdmi to manage our mysecual data base. This was automatically installed force by ma'am, so we didn't have to go through any set up there and we created a database and created a table inside of that database. Now before we create our second table. Let's briefly recap the terms that I just threw at you in the previous lesson, just so that they'll be fresh on your mind and also because some of the terms that I will throw at you in this lesson. Is somewhat similar to what we talked about in the previous lesson. So let's start with a database. A database is just a container of information for what is typically an individual application or a specific thing. So, we created a blog database. This would be a database for our blog application. That is what a blog is. It is a web application. So, inside of this database, we would have what are called tables to store individual logical groups of information or entities. So all of our user information would be inside of a table. All of our posts would be inside of a table. Comments would be inside of a table. Permissions and so on and so forth. So inside of these tables, we have what to call columns. So a table is made up of individual pieces called columns. These columns can contain numeric data. It can contain alpha. It can also contain binary or date time. There's a lot of different choices when it comes to creating columns. And inside of our users table. We have four columns. The first is the ID. This is the primary key of our table. Primary keys are very important because that is the primary way that you find records within a table. Every record will have a unique ID so that you can refer to that record very easily. And if you'll remember from the previous lesson, I said that every table needs to have a primary key. Even if you don't think your table needs one, it does because there's going to be some point withing your application's life that you will want to reference a table by the primary key. So we created one called ID it is an integer and it automatically increments that Id with every new record. The second and third columns are called first name and last name. These are text data, but they are limited in size. These are var char. And the reason why we wanted to limit them in size is because the database is going to allocate the full amount of storage that we specify. So we have a VAR char of 25. So for the first name, the database is going to create a column that can be 25 characters long. It can be shorter than that, but it cannot be longer than that. These limits are there. So that's you don't store more information than what you actually need. We also have an email, which is a bar chart as well. So in this lesson, we're gonna create a posts table one that's gonna contain all of our posts. So let's click on the new link over her on the left hand side. And let's call this table name. Posts. Now our first column is going to be ID, because we need a primary key. It is going to be an integer and we will just scroll on over and check this A I, the auto increment, this is going to pop up this ad index dialog. We'll just take the defaults. And now we have our primary key. So let's think about what we need. As far as a post is concerned. We have a title. Have content. It would also be nice to know when that post was created. And we also need to know who created that. So let's start with the title. So the title is pretty easy. This is text data, but we don't want to use the text type, because that is going to allocate a huge amount of space for the title and we don't want that. So we want to use var chart and let's see 25 5, let's make this 35, I mean, typically our titles aren't going to be very long, but 35 should be long enough. And we could also make this a nullable column, meaning that we could store nothing inside of this column, but every post should have a title. So we are going to leave the Knoll checkbox unchecked. The next thing is the content. Now the content is going to be a large amount of data. It might not be a large amount of data. Data, but we need to be able to store a large amount of data. So in this case, we can choose the text type. This also needs to not be null because every post should have content. And now, let's have the column that will allow us to know when the post was created. So we'll call this date_created. And we have several different types here. We have a date, we have a, Have a date time. And we have a timestamp a time and a year. Now, the date time and the timestamp are what we would be more interested in because we want to know when the post was. Was created that means the date and the time. Now a date time field is or a column rather, is going to store the date and time inside of that column. A timestamp is just a timestamp and you can read these toolboxes. A timestamp is the amount of seconds since the Unix epoch. So if you did a TIMESTAMP, you would have to take that TIMESTAMP and then convert that into a date and time whenever you're displaying information in the browser. So a DATETIME might be a better choice in this case. So we're going to choose DATETIME. A DATETIME field can be no But we don't want that to be no because we want to know when the post was created. Let's look at the default values. We have no and current timestamp. Now this might seem a little weird because we're using a date. Date time. We can also use the timestamp but one of the default values is current timestamp. Well, in our case, we want to use current timestamp. Now a default value is exactly what it means it's a default value. So that's whenever we create a new post record, the database can automatically populate the date created column. With the current time and the current date, and that's what we want. So we're going to have a default time or rather a default date and time based upon the current timestamp. And as I said earlier Earlier we do not want this to be null, but we also need to be able to, store the user who created this post. So we want to go up to the top, we want to add one column, so let's click on Go And we now have another column. So how do we reference our user? Do we do so by the email address the first name, the last name or combination between those? Well, no, we don't. This is where the primary key is. Comes into play. What we do here is we reference the ID of the user. This is called a foreign key. So in the case of our posts table, we have a primary key called ID and then we are going to have a foreign key Called author ID. So it's called a foreign ID because it is the ID of a foreign table. A foreign table is a table not of posts. So we're going to call this author ID. This is an integer. It cannot be no because a post has to have an author and that's going to be it. So, for our table comments, we don't have to put this if we don't want to, but we can say our blog posts. We are going to insert a new user. We don't need to put anything in ID. But we do need a first name. So let's do Jeremy. Let's do last name peak. And then let's say jeremy@peak.com. This is going to be a user that we are creating and we will click on Go And now we have a record inside of our users table. So if we look right here, we can see that we have an ID of one. First name is Jeremy, last name is Mcpeek. Email is Jeremy@mcpeak.com. Now that's not my email address, so don't worry about that. So if we wanted to change any of this information we could except for the ID, And it would still refer to the same user if you will. So that means that inside of the posts table, if we use the ID, a user can change their first name, their last name, their email address, and it is still going to refer to that same user as the author of a given post. Because IDs don't change, that is the whole idea of a primary key. And when we reference that primary key in another table, we call that a foreign key. So now we have a user's table and a posts table. And while we have some data inside of the users table, we don't really have any inside of the posts table. And so in the next lesson, we are going to start looking at actual SQL commands so that we can insert data into our posts table.