4.3 Creating Many-to-Many Tables

So far, we have covered a lot of the basics. We’ve talked about dealing with single tables. So now we know how to create those tables, get data in, get data out. And we have also talked a little bit about how to deal with the one to many relationship tables, where we have a guest table and an address. Where we have the guest table is gonna have one record in it for each person, but each person could have multiple addresses if we would so choose. And so that's how we linked these two tables together in that one to many relationship. And that's a very common relationship. And I wanna talk to you about one more common relationship that you're gonna have to deal with between tables when you're working with databases for any type of application. And that's gonna be the many to many relationship. Where we're gonna have many records in one table that can be associated with many records in another table, and how do we structure that? So let's say continuing with our party scenario, we have guests and those guests can have one or many addresses or none. Honestly, they could have no addresses or many addresses. Now we also wanna talk about what are these people bringing to the party? Because everybody has to bring a dish to pass, that's just the way that this works. So what we can do now is start to add in another table that's gonna be dish. And now we can associate who is bringing what. And we're going to allow multiple people to bring similar or the same dishes if they would so choose, that would be fine as well. So let's start by creating this new table. So we'll say create table. This is going to be dish, and in here we're once again gonna have an ID, this is going to be an integer, this will be NOT NULL. So you're gonna see a lot of the same things that we've already done before. We have a PRIMARY KEY, this is also gonna be AUTO_ INCREMENT, so we've seen that before. Now we're also gonna have, you can add in here as much as you want. And we'll just say that there's going to be a name, so the name of the dish. This is gonna be VARCHAR (50) maybe. And then you can add in as many other things as you want, really. So that's all I'm really gonna worry about in this case is going to be the ID that we can uniquely identify it with as well as its name. So now if I were to do show tables, we're gonna see now that we have three tables. So we have guest, address, and dish. So now let's say that there are people that want to bring different types of dishes. So in this case, maybe we're going to do some inserts, and I'll say insert into dish. And maybe we'll have a name that we're going to insert, we'll have values. And maybe somebody's going to bring a salad, and then we'll do another insert. Maybe someone is going to bring pasta, and maybe somebody's also going to bring garlic bread, sounds very Italian-ish. So now we've got a couple different things here so we can do a select * from dish. And we're gonna see now, we have a few things going on here. But what if we are gonna have a lot of people coming and we need to make sure that we have enough for everyone? So I'm gonna allow multiple people to have or to bring the same dishes. So how would we do that? So in order to make this kind of interesting, I guess, let's go ahead and bring in another person to our guest table. So we're gonna insert into guest. We need a first_name, a last_name, and a confirmed in this case values. We'll just call this ("John" "Wilson") and confirmed as yes. So now we're going to have multiple guests coming. So we have now Derek and John coming, and we're going to ignore the fact that I haven't confirmed yet, I'm going to come to the party. But now what we wanna do is we want to specify how we're going to link multiple people having to bring multiple dishes, or the same dishes, or some combination of them. So how are we gonna do that? Well, we can't really do it using just the tables that we have, we need to introduce another table. And that another table is gonna be the linkage between our guest and our dish. So whenever you're creating this many to many relationship, you're gonna have a table in between that's gonna help you manage that. So the way that you do that is you're going to create another table, let's go and clear this out. So I'm gonna create another table, and typically the way that you name this is using the table names of the two tables that you wanna bring together. So in this case, I'm gonna call this guest_dish. Now once again, we're going create this like we've done before, we're going to give this some values to link them. And if you recall, we have foreign key relationships previously where we were tying things together from other tables. And you can do something very similar to this, but we're gonna do this a little bit differently. We're going to reuse another concept that we had in another lesson where we were talking about primary keys. So in this case we're creating a table that's actually gonna have multiple primary keys. And that's very possible, you can absolutely do that in your tables. So now we wanna link together the guest and dish using primary keys. So in this case, we're going to say that we're gonna have a column called guest_id. So that's going to link the guest table. We're going to specify that that guest_id is going to be an integer, and it's gonna be NOT NULL. Then we're also going to bring in the dish_id, which is gonna be an integer, and once again it's gonna be NOT NULL. And if you remember before, when we were creating columns as primary keys, we were just tacking it on the end of the column definition. Well, you can also do it another way. At the end of your create statement, you can specify primary key as a parameter, or as a constraint, and then within here I can specify multiple keys. So in this case, my guest_id as well as my dish_id, the combination of those two is going to be my primary key. So we'll go ahead and close this off. And so now if I do show tables, you're gonna see now we have four tables. So we're having this intermediate table guest_dish where we can now link these two things together. So now we have that data, let's go ahead and start to link them together. So I have two users and I have three dishes. So let's go ahead and do an insert into guest_dish where we want to say the guest_id and dish_id is what we're gonna be putting in there. We're gonna specify some values and now we're just linking these things together with their primary keys. So in this case we're going to say that the guest_id is gonna be 3, so Derek is gonna bring a 1, so that's a salad. Maybe I will also bring some pasta. And then we'll have John who is number 4, who's gonna bring some pasta as well. And he is also going to bring some garlic bread, so that's gonna be number 3. So now if I were to do a select * from guest_dish. You're gonna see now that we have linkages between where we have multiple on one side from guest be able to reference multiple on the dish side and vice versa, so that's pretty cool. So now that we have all of this set up, how are we going to get data out of it using a select? Well in the next lesson, I'm going to show you how we're gonna make a couple little changes to our previous selects and joins to get these many to many relationships together.