First up, we will build a simple note-taking app. Now, of course, we could approach this in a number of ways, but at this stage of the game in our learning, why don't we begin at the database? I will add a new table, and yeah, if our goal is to track notes, well, note is the noun, so why don't we name the table notes? Okay, a note should have a primary key, and then we should also track the body of the note. So let's set the data type to, how about, let's go with text. It can be any length, and no, it cannot be null. And let's save that with Command-S.

It can be any length, and no, it cannot be null. And let's save that with Command-S. But finally, it would make sense that a Note belongs to a particular User. So with that in mind, why don't we also create a Users table, and a User will also have a primary key, and then a name. That can be a variable number of characters. It cannot be null. And then why don't we also, for good measure, include an email. And that too will be Barkar255, and it cannot be null. Okay, but now, here's something interesting to think about.

That makes no sense. So the email address always needs to be unique. Okay, so we can do that on the database level by adding a unique index. Let's go down here, and we'll add an index. Now we can let the index name be auto-populated, but I do want it to be unique, and the column name is email. All right, let's save that. Okay, so now, and by the way, I get it. This is a new concept, so just come along for the ride. We have introduced a unique index for the email column, and this does a number of things.

This is a new concept, so just come along for the ride. We have introduced a unique index for the email column, and this does a number of things. But right now, what we care about most is it enforces that only one User can have a particular email address. So why don't we try it out? Let's close this out, open my table, and we'll do myself, Jeffrey Wei, and let's expand these. Okay, we'll set the email to jeffrey@laracasts.com, and then Command-S to commit it. So now notice, if I have a different User, and we try to use that exact same email address, as soon as I save it, you're going to see an issue.

So now notice, if I have a different User, and we try to use that exact same email address, as soon as I save it, you're going to see an issue. And there we go. We have an error. There's a duplicate entry for this email address. And again, that unique index is what enforced that. All right, so let's undo this by pressing Shift-Command-Delete on the Mac. All right, so now let's move over to the Notes table and create a new note. And how about PHP for beginners is the best, and we'll save that. Okay, but now, of course, I'd like to create a reference or a relationship between this

And how about PHP for beginners is the best, and we'll save that. Okay, but now, of course, I'd like to create a reference or a relationship between this particular Note and, let's see, this particular User. So how do we do that? Well, I need to update the table to add what's known as a foreign key. Let's do that now. To the Notes table, to Structure, and let's add a new user_id. And this column will point to the User who created the Note. That's the relationship here. The Note belongs to a User.

That's the relationship here. The Note belongs to a User. And then what should the data type be? Well, it should be the exact same thing as what you did for the primary key. So in this case, an integer. Finally, can it be null? Well, ask yourself, would it ever make sense to have a Note that was not created by any specific User? And keep in mind, the answer might depend upon the application. In this case, we're going to say, uh-uh, it would make sense.

And keep in mind, the answer might depend upon the application. In this case, we're going to say, uh-uh, it would make sense. Every Note belongs to a Person. So no, it cannot be null. All right, so let's save it. And if I switch back to my Notes table and to Data, yeah, we can update this userId to point to a specific User. But actually, real quick, notice that at the moment, userId was set to zero. And there is no user with an ID of zero. So this is another example of potential pitfalls where your database consistency can

And there is no User with an ID of zero. So this is another example of potential pitfalls where your database consistency can fall out of whack, for lack of better words. We want to make sure that everything is consistent. And if we have a note written by a User who doesn't exist, that's a problem. So again, there are constraints that we can put in place to enforce this. And I'll show you how. For now, let's update this User to one. And then we'll go back to the structure to add our constraint. OK, so now on the user_id column, check this out.

And then we'll go back to the structure to add our constraint. OK, so now on the user_id column, check this out. If I scroll for a bit, you'll find a foreign key section. So let's create a foreign key. And yeah, this is not as complicated as it might look. So on the Notes table, this user_id column, well, what does that reference? It could be named anything. Does it reference something specifically? And the answer is yes. It actually references the ID column on the Users table, right?

And the answer is yes. It actually references the ID column on the Users table, right? That's what it points to. It points to a specific column on the Users table. So the reference table is Users, and the referenced column is ID. And then we have these hooks for what should happen when the referenced record is updated or deleted. So here's an example. Imagine you have a User, John Doe. And John Doe creates a bunch of notes.

Imagine you have a User, John Doe. And John Doe creates a bunch of notes. But then we delete John Doe's account. So now his User record is gone, but we still have all of these notes that were created by John. So we have these orphans now, notes that point to a User who no longer exists. So these hooks allow us to declare what should happen in those situations. So for example, I could say, well, if the User record is deleted, why don't we cascade and delete all of his notes in the process? And as you'll find, maybe that is not correct for your application.

and then I will save it. OK, so check this out. We have a User, Jeffrey Wei. Jeffrey Wei has created, let's go to data, exactly one Note. Actually, let's do one more. Hi there, blah, blah. And I'll point that to myself as well. OK, so now because we set up that foreign constraint, watch what happens if I hit delete to delete the User. That will now cascade and delete all of the Notes.

So in the next episode, behind the scenes, I'm going to populate this with a bunch of dummy records, and then we will display it on the screen. I'll see you then.