Video Transcription

Welcome back everyone. In this video, we’re going to be taking a deeper dive into relationship types between our tables. And we’ve talked about some of these in passing before when we talked about UML diagrams. So we’ve seen these as part of the homework, and throughout our various other videos that we’ve had in this class so far. But let’s take a deeper dive into what these represents. So the first one that I’m going to cover here is one too many. And so the one of the common examples that you’ll see with this is a parent child type of relationship. So this is something where one record can have many connections on the right hand side, so one on the left many on the right, this can be something in the form of department, and employee. Or it could be something like our customer, and order, as we’ve seen so far in our database. And so the one to many representation here between these two entities would look something like this. So we would have, for our departments, we have, we’re one we have a department that can have one or more employees, an employee belongs to one and only one department. So the double dash here. So this, this here is going to represent one and only one. And then this here is one or many, then for our customer and order we have, we have one customer who has, who can have many orders. But a customer may not have an order yet, right. And we’ve seen this as part of our queries with the wide world importers database, where some customers or some salespeople don’t have any orders or or sales and a particular year, or even on a on any particular day, right. So that representation looks something like this, and a formal UML diagram. So we have our one and only one, zero or many, one and only one, one or many. But some of this here is logical constructs only, meaning that I can draw my relation here in my UML diagram. But some of this cannot be related cannot be physically represented as part of our database, and SQL Server Postgres, whatever database you’re actually working with, now, particularly this representation here, between department and employee.

So I have one department that can have one or more employees. But how there is nothing here that can, when I’m actually writing my database, physical database, there’s nothing there that I can do to prevent a department existing without having an employee. So some of this representation cannot be physically represented as part of our database. However, we can enforce some of this using things like check constraints, and other forms other methods, and constraining data inside of each of our columns. But things like one to one or more, we can’t necessarily always enforce that representation. Now, zero or more is more representative of what actually happens in our physical database design. But it is important to draw your UML diagram correctly towards what the data actually is logically, because that helps programmers and other people who are interpreting and using that database, it helps them understand how to write the code that supports that relationship behind the scenes. So things like this will not necessarily be able to be restricted physically on the database side but can be restricted on the application side. Otherwise, relationships that we talked about so far are one to one. So an example of this is products and inventory. And so the relationship here, between product and inventory here is going to be I’m just gonna draw a line here. We have one product to zero or one inventory. We could also have one to one on either side, so the remember, this is one and only one. And then this symbol here is zero, actually type right out zero here, this is going to be zero, or one.

So drawing this out here, right again, basically, these can only be implemented with zero or one similar issue with our zero or many, right, we cannot actually, logic, we can’t actually physically enforce one and only one or one or many inside of our database construct that these are only logical representations. So physically, these can’t actually be represented. So here again, I’ll read this from left to right, we have one product is associated with zero or or one inventories. And then an inventory is associated with one and only one product, right. So an inventory logically cannot be present without a product, but a product can be can be present without an inventory. And then down here, a products can, one products can be can belong to only one and only one inventory. And inventory can be represented by one and only one product. So the difference between these two representations here is that my product on this line here can’t exist without a corresponding inventory. Up here, it can, my product can be alone, my product can exist without the inventory there. And again, some of these representations are logical only physically, we can actually enforce all of these relationships. And I’ll try to point these out like I did here, with the one and only one. But some of these logical representations can be enforced through check constraints. And we’ll get to that here in a little bit. But the last typical relation that we could have is many to many, so many to many, is typically going to be implemented using a linker or bridge table associated with it. So many too many is going to look something like this. So we have something like that, and you can have a one or zero there. So we can have one or many or zero or many.

Now, as I mentioned, right thing, these are typically represented using what we refer to as a linker table. So this right here is a linker, or bridge table. The primary reason why we actually have the linker or bridge tables here is because many too many itself can’t be physically represented in our database, right? Physically, this cannot be implemented. And so in order to physically implement this relationship, we actually have to enforce something like this, where we have a intermediate table or aka link or or bridge table that represents that many to many relationship. So if I read this from left to right here, a product can be in zero or more product locations. And a location can also be in more than one product location. And so what this enforces here is that we can have many products at many locations. Right? But this table is the representation of that relationship, right? This table is that representation in our relationship. So anytime, best practice here, anytime you have a database that has or a diagram that has this many to many relationship here. We are typically going to redraw that using a link or table. So if you ever do see that and in practice, just the many to many, just understand that underneath the hood and the actual raw implementation. There’s going to be be some form of link or bridge between those two tables. But that will conclude all of our standard relationships between our tables, and the following videos we’ll talk about some different variants among these types of relationships.