Now that we have a users table we're going to start taking advantage of the entire relational data structure that's available with MySQL. In order to do that we need another table, you can't have one relationship without having multiple tables. We're also going to talk about foreign keys.

Now come down to the schema list and right-click ‘Tables’ and click create table.

I'm going to call this table our addresses table. This is going to be a situation where a user can have many addresses, and the address is always going to belong to a user. The very first thing we're going to do is to create addresses Id addresses_id. Now think back to what we did in the Users table, it's going to be the exact same! This is going to be the primary key. We're also going to select not null, unique, and auto-increment.

The next column we're going to make is addresses_street_one and we are going to select not null. There should never be a situation where addresses_street_one should not be a required value. I'm going to keep the datatype at varchar and set it to 45. I'm going to do the almost same thing for adresses_street_two but there will be some differences.

addresses_street_two will be where someone would put a secondary corporate address, a unit number, or an apartment number. The data type will be the same but we're not going to make this required. Make sure you don’t have not null checked. We have just a few more columns to add.

The next column we're going to make is addresses_city this one is going to be the same as addresses_street_one. We're going to make it required to have city. We will then make another column and that will be addresses_state. The only difference between the state and the city is that we are going to limit our varchar of two characters. We're going to pretend this is only going to be in a US application. So it's always going to have an abbreviation like A.Z for Arizona, and N.Y. for New York. We’re going to make sure that this is not null. Then the next column we're going to make is postal code, addresses_postal_code and it will have a varchar.

Now you may wonder why this is going to be a string data type? This is because usually when you think of postal codes having integers in them. This would technically be correct for many cases in the U.S. However we can't really trust that because what if someone is used to putting a dash followed by the four-character extension in their postal code. This would run into a little bug so we're going to not allow for that. We're going to have a little bit more flexible interface for our validations. I'm going to limit the postal codes varchar to 20 characters.

Now this by itself will give us our table but notice that we don't actually have a connection here between addresses table and the users table. it's not magic and it's not going to happen all by itself. I am going to create one more column for this table. This is going to be called addresses_users_id. Now this one is going to be of type integer. This is going to be our way that we connect the two tables. We are not going to make this a primary key and we're not going to make it unique. The reason is because we are going to have situations where the same address or different addresses can belong to the same user. So all of this is going to be exactly just empty.

Now we can come down to our foreign keys tab and click it.

Here we can select addresses_users_id and you can see that this grabs us our columns on the right. This is going to be the foreign key and then we need to tell it what to table to reference. So if I click under “Referenced Table’ and then click the users table. This is going to say I want to map the adresses_users_id to reference the “users table”. You could also do this manually but this makes it nice and easy. We're not done yet because we need to tell it which value or which column inside the users table to map to. So now we can say I want this to map to the user's ID.

This is actually going to work. Now some other things you could do is save things like on delete and you may want this to cascade.

So in other words what that would mean if a user's deleted then we want to make sure that all of their addresses are deleted as well. That is usually a best practice, so we're going to do that. Select Cascade! This is all we should have to do. So if everything there is setup properly if I click “Apply” this should bring up a lot of code.

Let's kind of sift through this before we just hit apply. What this is doing is it's creating a table for us in our schema of addresses. There it takes in all of the different column types such as our id, and it makes sure that it is an auto-increment. That it's not null and it’s an integer value. The other ones are pretty much what we've already covered. Then here for addresses, it is for the addresses_users_id that's going to be an int.

Then if you come down we have our primary key being sent to our addresses_id, and then we have our users_Id set as an index right here. We have also our constraint that says addresses_user_id, Foreign key as addresses_user_id. This says the reference is to the user_id column inside the users table. So now if I click on apply this looks like it all worked. So if I hit close we now have two tables. We have a “users table” and we have an “addresses table”.

This has everything that we just put inside of it. So this is working nicely. We now not only have another table but we have a table that references our other one. So this is the beginning of learning about how we can create a true relational structure inside of our application.