Hey everyone, welcome back. In this video, part seven of topic five in our database class, I'm going to discuss several considerations for how we can model many to many relationships in real world databases. And now we'll take a look at many to many relationships, and we've already seen how to do this several times throughout our class together, but as was the case with the one to one relationships, there are some interesting nuances and additional details that we need to consider when we actually think about how to implement these many to many relationships in our real world database. So as we now already know, hopefully this is review by now to actually implement a conceptual many to many relationship, we need to add a third table in between them. And that third table is called an intersection table or an associative entity. And an associative entity is an intersection table that has a specific characteristic that I will discuss shortly. So let me put it this way. Every associative entity is an intersection table, but not every intersection table is an associative entity. So there's something special about an associative entity. And so it is an intersection table, but it's an intersection table with a specific characteristic. Note that some people, including me from time to time, will refer to an intersection table as a lookup table because that's its purpose. It's just there to keep track of the relationships to allow us to look up how the connections between the associated tables are linked together. Okay. All right. So an intersection table, typically, commonly, I highlight typically here, has a composite key, composite primary key, and that composite key will consist of the primary keys from each of the parent tables to which it is connected. Now, each of those of course will also be serving independently as a foreign key link back to its parent table. However, typically means usually, but that suggests not always. And so we don't always have to have a composite key in our intersection tables or our associative entities. Okay. It is perfectly possible and sometimes desirable to use a surrogate key in an intersection table. Rather than using a composite primary key. And I'll give you some examples here in a little while of why that is the case. But before we get to that particular little nuance, let's just look at some, these many many relationships. And in this case, we're just implementing them with a single intersection table. So a simple design, in this case, we want to keep track of which students are taking which classes. Nice. So I've got my student table over here. As we can see, this is a strong entity. And we've got our class table over here and another strong entity. And then we have our intersection table in between, which allows us to look up which students are taking which classes. And down here on the bottom, we can see a data representation of this. So I can have my say student name or student numbers here, student ID, student names. And then I can have my class numbers and class names. And then I just have these various linkages that keep track of, you know, every row keeps track of a connection between a student and a class. So this tells me that student number 100 is enrolled in class number 10. And I can say student number 200 is also enrolled in class number 10 and also in class number 30. And student number 300 is in classes 30 and 40. So it's just a look up table right an intersection table, no big deal. Do note that this is an ID dependent week entity. Because in order to add a row to this student class table, I am relying on the keys from the connected parent tables. So this is a classic intersection table, the only attributes, the only columns in this intersection table are the keys. So the difference here of between an intersection table and an associated entity is whether or not we have any non key attributes. Currently, if we look at this design, the only attributes in this table are key attributes right there's no non key attributes. But if I have a non key attribute, then this intersection table qualifies as an associative entity that's criminal vocabulary perspective the difference between them. So if I did something similar to what we saw in our earlier explorations of this topic, maybe I put a, I don't know like a letter grade. Column out here now this would be an associative entity. So we have an association relationship, I'm not only keeping track of which students took which classes, but there's an additional piece of information about that relationship that I'm tracking in this case, the grade that the student earned in the class. So if you have something like this, it is not an interest, well, it is an intersection table, but it's a special type of intersection table called an associative entity. So if you have a lookup table or you have an intersection table that contains non key attributes as we see here with the letter grade attribute. Right, then this is an associative entity rather than an intersection table or a lookup table. We're storing additional information about it, but do keep in mind that what we're modeling here conceptually is in many to many relationship between students and classes. So conceptually, what we have is something that looks like this. Right, but to implement it in a real world database, we need this intersection table or as illustrated here in associative entity to sit between them. Do note also that conceptually the many ends of the relationship are touching the two tables, but when we implement an intersection table or in this case an associative entity, the many ends will touch that third table that new table and the other side will be ones cool. Again, we've seen how to link these together. This should hopefully be review on how to join in this case three tables together. If we assign the letter n to represent the number of tables being joined, then you will always have n minus one linkages between them. We have a three table solution involving the student table, the student class table and the class table. That means there will be two linkages. Right, so we have matching values of student ID and matching values of class number in order to link them together. So two connections in order to link together three tables or more generally n minus one connections for n tables. And of course we're doing it down here using the join syntax, which is the one that I prefer and that we will still have two connections between them. Right, so here we have a connection between student IDs and here we have a connection between class numbers. And when we have more than one table, there will be more than one join statement and minus one of them in fact. So hopefully that is review the sequel on it. But now we can expand our knowledge of many of many relationships and how to model those so that they can work in a real world database by talking about one of these interesting nuances. So we have a single odd scenarios and this is a scenario where we choose to use a surrogate key rather than a composite key when we are having an intersection table or an associated entity. So what are the implications of this choice. Let's explore those. So first textually the implications are described on this slide. That is if we used a composite primary key, which is the method that we see and that we have seen most frequently throughout our database class, we know that the combination of values. For the two columns that comprise the primary key that specific combination of values can appear only once in the associative entity. So if I back up two slides and we return to this scenario where we have a student class table with a composite primary key consisting of the combination of student ID and class number. The combination of a specific student ID and a specific class number can live in this table or can exist in this table just once. So if I, for example, have a student number 100 who takes class number 10, that combination of values can exist in this table just one time. And functionally what that means in this class registration scenario here is that each student could take a particular class just once. There is no way in this design to allow a student to take the same class more than once. All right, because if I tried to add another set of values in here for say student number 100 and class number 10, remember it's a composite primary key. So that primary key would already exist and the database would throw an error right it would not allow me to enter that row of data. If I tried to do this, I would get an error, this would not be allowed. So effectively what that means is that each student in this scenario can enroll in a particular class just once because I have no way of allowing students to take the same class more than once. Now that may or may not be desirable depending on our scenario right if we're I don't know some kind of university that probably is not desirable because we would want to have the capacity to allow the same student or a particular student to take the same class multiple times. So maybe they get a failing grade on their first attempt and they want to have a second attempt this design as we see here would not allow that. So this is when we need to start considering when is it appropriate to use a surrogate key rather than a composite primary key as we see illustrated here. So if we have an intersection table or an associative entity a specific type of intersection table and we choose to use a surrogate key instead of a composite key then each possible pair of values. Like each set of each combination of I don't know say student ID and class ID can appear many times. In that associate events, or in that intersection table. So let's compare a couple of alternate designs using another real world common real world scenario and see if we can intuitively develop the sense for why this is the case. Now with this top design. You can see I have a relationship between customers and products and I want to allow customers to rate products. And it's a many to many because each customer can rate many products and each products can be rated by many customers. So conceptually we have a many to many relationship between products and customers. And so we have a conceptually we have this but to implement that we need an intersection table in this case and associative entity because we want to allow them to record additional information about that connection and that is their actual rating of the product. So I don't know maybe this is a maybe they rated on like a one to five scale so this these rating values would just be the numbers one through five. So this top design we are using a composite primary key. So we have a design in which the combination of a specific customer ID and a specific product ID serves as the primary key in the rating table. And each of those values individually. Is serving as a foreign key link back to its parent table right. However, as we saw as we learned on the previous slide when we do this with a composite key. The specific combination of a particular customer ID and a particular product ID can only occur one time in this rating table. So what does that mean from a business perspective well, it means that with this design on the top. Each customer would be allowed to rate each product just one time. So I could only have a single rating from each customer for any individual product and maybe that makes sense. Because we know that there are lots of people out there that like to try to do things like manipulate product ratings so that they can sell more of them. So you can imagine an alternate scenario where I allow a customer to rate the same product many many times. Well, if they are a bad actor and they're trying to game the system, they might go out there and record 1000 positive ratings for a particular product. Or maybe they want to cause problems for their competition. So they go and they put 1000 negative ratings for their customer or their competition's product out there. So with this kind of design, the one on the top that would not be allowed each customer could only rate each product once. The design of the database would prevent the same customer from rating the same product more than once. However, we have an alternate designs and that is a design in which we use a surrogate key surrogate primary key in our associate events see as we see here. So in this design, we have a rating ID which is just a surrogate primary key right and then the customer ID. And product ID attributes are no longer part of the primary key instead they're just regular foreign key links. Back to their respective parent tables. Okay, so in this kind of a design, a specific combination of customer ID and product ID. Could appear in our reading table many many times. Each time there would be a different reading ID because that's the primary key, but the particular combination of customer ID and product ID could be in there many many times. So let's just quickly take a look at a data version of this. So let's just repeat what we saw except use a data view of the same scenario of that same rating table. Okay, so I'm going to have a reading table. And in this design, we're going to have a rating ID. Which is our primary key surrogate primary key and then we might have a customer ID as a foreign key. And product ID is a foreign key. So in this design from a data perspective, we could do something like this customer ID. I forgot to put the rating attribute in there. Sorry. We also have a rating attribute out here where we keep track of the customers actual opinion about that product. So with that in mind, so maybe customer number one rates product number one and they give it a rating of five. And then I have customer number two, who rates product number one and they give it a rating of three. And then let's say we have customer number one and they rate the same product again. And this time they give it a rating of one. And then I have. That'll no customer ID one and product ID one again and this time they give it another rating of one. So you can see with this kind of a design because the primary key. Is a surrogate rating ID. The same combination of values for customer ID and product ID can appear in this table. Several times because they. These attributes are no longer part of the primary key. So with this design, each customer could rate the same product multiple times. And maybe that's okay based on our particular use case. It always just depends on our use case, right? If we want to run our business in such a way that each customer could. Rate or review a particular product just once. Then we can implement that business rule with this design. If, on the other hand, we want to allow a particular customer to rate or review a particular product multiple times. Then we can implement that business rule by using this design. And there are plenty of cases where this might be reasonable. Like for example, say it's something that is consumed. Comparatively, rarely, like instead of generic concept of a product. Maybe what we have is a hotel. I like most people are not out staying in hotels. Frequently. So maybe I'm the customer. And I travel to, I don't know, New York. And I stay at a particular hotel. So imagine that this is not product, but instead is a hotel. So let's say that we have a hotel and imagine that it's hotel ID, etc. So in this kind of a design, it might be reasonable for me to allow each customer to rate the same hotel multiple times. So maybe I stay at this hotel in New York and that stay. is just average. So I give it a rating of three. And then maybe new management takes over and they invest a lot of money in refurbishing the rooms, updating the rooms and they get, they train their staff to be more courteous and more attentive to customer needs, etc. So the next time I stay there, it's a better experience. And I can add a new rating for the same hotel. Maybe this time I give it a five instead of a three. Okay, so there are scenarios where it might be reasonable to use a design like this. Right, where we use a surrogate primary key in your intersection table or in your associate events, the in this case, in order to allow the combination of values from the connected tables to appear multiple times. It's a subtle difference, but it's one that allows you to implement two different business rules, depending on the way the company does its business, we could do it one way or the other. And again, here we're using this rating scenario with the same thing would apply to any kind of scenario, right, the one we saw earlier was students and classes. So if I had taken this design and instead of using a composite primary key here, right, if these were not part of the primary key and instead we're just regular foreign key links. And I had some other attribute in here, a surrogate to use as the primary key, then that would allow each student to take a particular class more than once, which in a university scenario is probably a desirable way to do it. We want to allow students take same class more than once so that if they don't do well or well enough on their first attempt, they can try it again or maybe even take it a third time. So there are plenty of times out there in the real world where this is an important consideration. How many rows for a particular combination of the two connected tables do I want to allow to appear in the intersection table. If the answer is more than one, then I need to use a surrogate key.