Hey everyone, welcome back. In this video, part five of topic five in our database class, I'm going to discuss several different considerations relating to how we can model one to one binary relationships in our database designs. So we're going to begin by considering a simple relationship, that is just a one to one relationship. And the idea with a one to one relationship is that each row in one table can be related to a maximum of one row in some other table. So it's a one to one relationship. And we've seen plenty of examples of these before, things like, I don't know, like an employee and a parking space, or like an employee in an office. That's a one to one relationship in most businesses. Like most businesses, you wouldn't have a situation where each employee you can have multiple parking spaces or one employee can have four offices. Like that could happen, and I'm sure it does for time to time, but the usual case would be that they have just one parking space or one office. So these are just examples of one to one relationships. Now, when we have a one to one relationship, just like any other sort of binary relationship, we are going to keep track of the connections between the rows in the two different tables by using matched pairs of values in a primary key, foreign key relationship. So we take the primary key from one relation, and we drop it into the other as a foreign key, and that allows us to keep track of things. However, now that we know about minimum cardinalities, there's some additional things that we need to consider here. Okay, so if I have a one to one relationship, remember that one to one relationship is defined by the maximum cardinality. But we also know about minimum cardinalities, and we know that the minimum cardinality could be zero or one, and based on whether the minimum cardinality is zero or one, that will tell me where to put my foreign key. So if both sides of the one to one relationship are optional, that is, if the minimum cardinality in both directions on the relationship is zero, then it doesn't matter where you put the foreign key, but you can put it anywhere you want, anywhere that feels natural to you. However, if only one side of the relationship is optional, then the optional side receives the foreign key. So let me draw some pictures so we can get a sense based on our knowledge of database symbols, cardinalities, et cetera, what we're talking about here. So let's say we have these two tables and they're related to each other. Right, and these are one to one relationship, so the maximum cardinality will be one on both sides. And then the issue is what do we do about minimum cardinalities? So if we have a design like this where the minimum cardinality is zero on both sides, then it doesn't really matter where we put the foreign key. We could drop it into this table, or we could drop it into this table, doesn't matter. Right, either way works just fine. However, if we have a design that looks something like this, where there is a minimum cardinality of zero on one side of the relationship. So it's optional here, and the minimum cardinality is one on this side, so it's required here. Then in this case, we always always put the foreign key in this table, the one that has a zero for the minimum cardinality. Let's see if we can look at this from a data perspective and understand why this is the case. Right, right. So consider these scenarios, these examples given here, we'll start in the upper left, where we have a entity relationship diagram that shows the connections between employees and lockers, and the note that this is optional in both directions. So the way that we would interpret this relationship is an employee may have a locker. Each employee may have a locker, not required. They may have a locker, but if they do, they can have a maximum of one. Similarly, if we look at the relationship in the other direction, we can say that each locker may be assigned to an employee, but it doesn't necessarily have to be. Right, if we have 20 lockers and 10 employees, well, 10 of the lockers are going to be unused, so they don't need to be assigned to an employee. But if a locker is assigned to an employee, it will be assigned to a maximum of one employee at any moment in time. So this relationship is a one to one, but it's optional in both directions. And in this case, as we saw in the previous slide, it doesn't matter where we put that foreign key. So we could put it here, we could take the employee ID out of the employee table and drop that as a foreign key over here in the locker table. And that would allow us to keep track of the relationships between employees and lockers. Right, because I can say which locker is assigned to which employee all I have to do is match up employee IDs in order to figure that out. However, we could also do the sort of design that we see over here on the right, the upper right. This is an alternative design. It's still the same two tables, employee and locker. It's still the same relationship, right, zero to one on both sides. And in this case, we're keeping track of the connections between employees and lockers by dropping the locker ID here into the employee table. And we can figure out which employees or which locker is assigned to which employee by looking at matched pairs of values here. Of course, in both of these cases, the foreign key would have to allow null values, because the minimum is zero. And same thing over here. We did this way. We have to allow employee ID to be null here in the locker table. But you have a choice as a database designer, right? This method works just fine. And this method works just fine. There is no advantage or disadvantage from like a database or an efficiency perspective to doing it one way or the other. It's just which makes most sense to you. So does it make more sense to you to say that an employee has a locker in which case you might use this design, right? An employee has a locker or does it make more sense to you to say that a locker has an employee in which case we would use this design over here on the upper left. So it just comes down to what seems most logical and reasonable to you. It doesn't matter from the database perspective. But again, this sort of choice that's available to you as a database designer only occurs in these one-to-one relationships if the minimum cardinality is zero, or that is the relationship is optional on both sides. In that case, you can choose. However, if you have a situation like the one that we see down here where let's say that we're keeping track of patients and the beds in a hospital. So we have patients that go to the hospital. Some of them will need to stay in the hospital overnight. Many patients don't. If you, I don't know, maybe you're cooking dinner and you accidentally cut your finger pretty badly and you go to the emergency room. Well, you go to the hospital and they're going to, so you're a patient at the hospital, right? And they're going to probably give you some stitches and send you home. So you're not staying in the hospital overnight in that scenario, right? So not all patients need a bed. Okay. However, if a patient does need a bed, we only want to have each bed assigned to a maximum of one patient at a time. So let's take a look at this relationship. In this case, note the key difference is simply that it's mandatory on one side, right? This is one and only one and it's optional one on the other side, zero to one. Okay. So if we're reading this relationship in this direction, we would say each bed in our hospital at any moment in time is assigned to zero to one patients. So the bed may be empty, right? And we would hope that it would be because that means that people aren't getting really, really sick or injured. So the possibility exists for a bed to not be assigned to a patient. And that's how we would read the relationship in that direction. But in the other direction, we would say that it's a patient. If they are, if we require them to stay overnight, they're going to be assigned to one and only one bed. So if we have these patients and maybe they're having some surgery or something like that and they have to stay overnight. We are going to assign them to a bed and they must have one, right? It's not optional in this direction. A patient must have a bed if they're going to, I don't know, have some like heart surgery or something, but they can only have one bed, right? We're not going to assign them to many beds. We want them to be in just exactly one place. So if you have a scenario like this where it's a one to one relationship as defined by the maximum cardinalities, but it is optional in one direction as defined by the minimum cardinalities, then in that case, we always want the foreign key to go into the table that is on the optional side of the relationship. This case, it's the patient side. So we would put the bed ID into the patient table rather than putting the patient ID as a foreign key into the bed table. And the reason why is when we have this mandatory situation here, every patient must have exactly one bed. It means that we will never have any null values here, right? There will always be a bed ID for every patient because every patient has a bed exactly one of them. However, it would not be true in the other direction. So if we had done something like, I don't know, let's say that we decided that we wanted to put patient ID over here as a foreign key. I'll mark that as a FK so that we know it's serving as a foreign key. So this is like an alternate design and one that is not recommended based on what I've been telling you. So in this case, we would get rid of that and we would try to establish the relationship between these two tables as you see illustrated here. And this would now be our bed table and we would have patient ID there as a foreign key. So if you do it this way, it's would it work? Yes, technically it would work, but because this is optional, that means that if we have empty beds, then we're going to have lots of null values for patient IDs here. So it's a little less computationally efficient, right? We're having to have null values and as we know, the only time that we want to allow null values is if we have a very specific and defined reason for doing so. the point is I don't need to use null values to keep track of the relationship between patients and beds. If I choose this design over this design. So in this case, if it's optional in one direction only, the foreign key goes on the optional end of the one to one relationship. One last thing that maybe some of you have pondered and that is what happens if you have some sort of design where it is mandatory in both directions, right? I have one and only one as my cardinalities in both directions rather than having a zero. While this gets into some philosophy, there are some database designers that say that you should never have something like this because if it truly is a one to one relationship and it's always required, why do I have two separate tables? I would just say that instead of having like a bed to table in this design, if it's mandatory one to one in both directions, I would just not have a separate bed table. Get rid of that entirely. Just get rid of this entirely. No relationship here. It's not necessary. And I just make the bed ID and the bed location, a property of the patient instead. So I already have bed ID over there. Let me get rid of that. It would no longer have to be a foreign key, right? Just a regular attribute. So each patient is always required to have a bed. Each bed is always required to be assigned to exactly one patient. That's what that one and only one of both directions means for the cardinalities. And then we would just put the beds location over here and make these properties of the patient. We just have a one table solution. So you know, but again, this is just a matter of opinion. I tend to agree with this philosophy, but that's just, again, it's my opinion. Certainly from a like a technology perspective, it is absolutely possible to implement this here in a real world database like SQL Server. But the point is, if it's going to be absolutely required, always in a one to one relationship, you can just collapse the whole thing into one table and not worry about it. This top scenario where we have two alternate designs and there's really no advantage one way or the other to doing it has some implications for the SQL statements that we might choose to use in order to actually implement those relationships. That is, if we want to join the tables together and we see those here. So based on whatever decision we make, it's going to affect the SQL statements that we have to write. So if, for example, I have chosen to place the locker ID in the employee table, then if I wanted to join the tables together, I would need to do so based on matching values of locker ID. And we see that done here in this case, an inner join in the where clause. Right. So we're linking the tables together based on matching values of locker ID. If however, I had chosen the alternate design, which was to place the employee ID in the locker table, then of course we would need to do the join based on matching values of employee ID. So in this case, again, we're doing an inner join here using the where clause. And as we learned back in our third topic in the class, when we were studying SQL, we can also do joins using the inner join syntax in the from clause. And so what we see here, where we're doing a join based on matching values of locker ID is functionally equivalent to what we see here. Right. These two statements will produce identical results. But the point is it doesn't matter. The design choice doesn't matter. So it will work perfectly well either way. But when we ultimately decide on one of these two options, it has implications for the SQL statements that we write. So in this case, if we had chosen to place the locker ID in the employee table, we would have to do the join based on matching values of locker ID, which is what we see both here and here. Or if we had made the choice to put the employee ID in the locker table, then we would do the join based on matching values of employee ID, which is what we see here. So the design itself doesn't matter in terms of functionality, but it does have implications for the SQL statements.