4.1 Creating One-to-Many Tables

So now into the point where we have have a basic guest table and we can insert some data some data in there, but what if there is some additional data that we want to associate with our guest. So let's say for instance that we wan to track the addresses of our guest so that we can send out reminders, or we can send thank yous, or what have you. Now, what we could do at this point is we could alter our guest table and then add some columns on the end of this that would allow us to store additional pieces of information like address, city, state, zip, and things like that. But then the problem becomes we could ultimately have some duplicate data and that's just kinda get a little bit messy. And to add data to this and just continue to grow the tables with columns kind of makes things a little bit messy. So when we want to add some additional structure to our tables, we can start to link tables together using what's known as a foreign key. So I'm gonna show you in this lesson what it means to create a supporting table or another table that can be linked to our guest table and that's gonna be the address. So let's start by creating a simple address table similarly to what we've done before. So we're gonna say create table and we're gonna call this address. And then within here, we are going to create some fields. So I'm going to once again say that we have an id that's gonna be an integer, it's going to be NOT NULL, it's going to be our primary key. And we're also gonna once again use the auto increment on this one. And now I wanna add some additional columns. But what we also wanna be able to do is we wanna make sure that there is a way to link this to a user. Or to have one person be able to have many addresses if that's how we would like to do it, and so we wanna be able to track this information. So what we're gonna do is we're going to put another key in this table, that is going to reference our guest table. So what we're gonna do now is we're going to add a guest_id, that's gonna be an integer. And I don't need to worry about making this be a primary key or anything like that because this is not the primary key of the table, it's simply linking back to our previous table. But that being the case, I wanna make sure that this not null. Then we can add additional pieces of information in here. We can say address is going to be a VARCHAR, and we'll say 100. We can make that a little bit bigger, and then we can do things like city is going to be a VARCHAR, and we'll make that a 20. Now obviously, you can size these things appropriately based on what you need to do. We can say state, this is going to be a VARCHAR, we can make this 10. And then we could also make a zip in here, so we'll say VARCHAR for the zip. We'll make that 10 as well. So once again, you can make these things sized appropriate for your needs. Now, we could just end here, but what we wanna do is we wanna make sure that the tables know that this particular table, this address table is going to reference back to our guest table using this guest ID. And the way we do that is by creating a foreign key. So I am going to add a comma here, and I'm gonna drop down to the next line. But in this case I'm not gonna be adding an additional column here, I'm gonna be creating what's known as a constraint, and this is going to be a FOREIGN KEY constraint. So I'm gonna say FOREIGN KEY, and then in parenthesis, I'm going to specify what column in this table is a foreign key. And in this case it's gonna be guest_id and then we need to specify what that reference is. So we'll say references and then we can specify what particular table which it's gonna be guest. And then what is the key that it is referencing back in that table which is going to be a primary key which is going to be our id. So in this case, we'll simply say it's referencing id. And then at the end of that, we can close our initial parenthesis from the beginning of our create table command, we will add in a semicolon and we'll hit Enter. Now we see query, OK, zero rows affected. So let's go ahead and do show tables and now you see we have two tables here. Now, what we're gonna wanna do is we're gonna wanna be able to add information about this user into our addresses table and we're gonna do this like we've done anything else before. So let's go ahead and give that a try. So we're going to insert into address and we are not gonna specify the id cuz, once again, that's auto incrementing. But we are gonna have to specify a guest_id. And we're gonna specify the address, we're gonna specify the city, state, and zip. So now that we have that, we're gonna specify that we wanna insert some values and what are our values? Well, we'll just throw in 123 main street obviously this is gonna be valid data for you wherever you need it to be, and we will say that this is in somewhere, New York. And I actually forgot the guest at the beginning here so we need the guest_id which is going to be 3. 123 Main Street somewhere, New York, and this is gonna be 99999. There we go, just like that, so now we have our insert. And now we have values in our table, so now I can do a couple selects. So we already see at the top that we have our guest, and now I'll also do a select * from address. And we're gonna see that we have a single record in there. So we have our primary key which is gonna be the id which is one. This is going to be referencing our guest_id three, so Derek Jensen. And then we're also going to have this additional information, we have 123 Main Street, Somewhere, New York 99999. So now we have our data in these tables. Now it kind of starts to beg the question, what are we gonna do now when we want to retrieve data out. And I want to get out all of the guest list with the information from the additional tables, in this case address, how am I gonna do that? And we're gonna talk about that in the next lesson.