Video Transcription

Welcome back everyone in this feeder, we’re going to be talking about some variants of our standard relationships that we have inside of our database. So the standard relationships, one, or only 1010, or many, many, many of those sorts of things. So, first one that we’re going to talk about here is how we represent multiple relationships between two tables. So generally speaking, right, we can have a table that has more than one relationship with another. So we have a center table, something like like this. So we have an end, let me switch back to here. So we have an employee. And then an employee may have a role. And then we have departments that the employee belongs to. And we have this connector between the three. And so we have this kind of relationship here, where there is more, on the on the left hand side, we have one or only one. But here in the middle, we have one or more. So we have this linker table, right, if we have, we have an employee may have more than one role in the department, right? And so to represent that multiple information, because it because we an employee can have more than one role, and more than one role is associated with a particular department. So how do we represent that information. So we put all of that down into its own table. So we have our multi way relationship here, right, we have our employee table. And just now remember, we are using surrogate keys, I could in fact, use email as a primary key here, because it is a unique field. But right it is a natural key. So we don’t want to use a natural key as our primary key here. So we have employee ID.

And remember, all of our keys have the name of the table associated with it. But all the other columns do not. So we don’t have employee first name, we just have first name. And primary reason for that is when we go down here to do joins, things like that. The keys are important to have the name as part of it because it helps writing those queries a little bit easier to read and easier to do overall. But then we go down here, and we have our departments as well. So departments department ID, and and name. names should probably be a unique key here. So we don’t have any department who has the same name. And then we also have roll. So role ID, role name and role description. And then we can have an employee that is associated with more than one role, which is also says can be associated with more than one department. And so this is a really unique way or interesting way to represent this relationship. If an employee has, let’s say, one role to accomplish and department X, and a different role to accomplish and department a, we can actually represent that information here. So again, this center table is not actually a physical representation of the data. But this here again is a linker table, linker or aka bridge table. So very similar to how we represent a many to many relationship between two tables. We can also represent a many to many relationship between three tables by using that link or table as well. And we can connect more than one table together in one table by using more than one foreign key here. So you’ll see that I have SK one f k two f k three.

That’s because each of these foreign keys references a different key in a different table. So roll ID represents roll ID employee ID to employee ID and department ID to department ID here. And again, remember I’m using some best practices here. My foreign keys are named after the primary key In the originating table, this makes it significantly easier to track back to the or the origin points, or the origin table for that key. This not only helps us in our database design, but when you actually start programming this in part of an application, it also makes writing your application code significantly easier as well. So let’s continue on to our next relationship variant. And this case, we’re talking about multiple roles. So this is somewhat like I did before, where I have multiple foreign keys for multiple from, or sorry, the the table I showed here, I have multiple foreign keys down here from more than one table. But in this situation, I’m going to have multiple foreign keys to the same table. So for example, we have the relationship here, between con a customer and contact information. So what we actually achieve here is two foreign keys foreign key one foreign key two, but primary contact, secondary contact both of these. So both of these, reference, the contact ID, both of those reference the contact ID. So a primary key can be the foreign key in another table in multiple references. So we are not restricted to using a primary key as a foreign key once and only once. We can use it multiple times and doesn’t have to be used multiple times in different tables, it can be used multiple times and only a single table as well. So this works out fairly well. One of the caveat here is how do you prevent both from being the same? Right? How do you prevent the primary contact from also being the secondary contact? Well, generally speaking, this can be done using a check constraint. And I’ll showcase these, I’ll showcase these in, in some feature examples. We’ve shown we’ve shown check constraints before as part of our lecture series. But we haven’t really got a chance to really implement our own yet. But if if it’s just simply doing something like this, where we’re checking to make sure that the secondary contact is never the primary contact, and vice versa, then we can achieve that with the check constraint.

So what’s next? Self referencing entities, this is a kind of an awkward one, we can have an entity like employee reference itself. So we can have an employee that references itself. This is kind of a weird one, right? So for example, how do we have a how would we represent someone’s manager as part of an organization? Well, a manager is nothing more than just another employee, right? And so we don’t want to necessary we don’t necessarily need another table to to store, just the managers, because the manager is just an employee as well. So what we can actually represent here is that a manager, right, the manager ID is nothing more than a foreign key to employee ID. So not only can we use a key as a foreign key multiple times in the same table, or an external table, or multiple external tables, but we can also use a primary key as a foreign key within itself, which is kind of weird to think about, but it works out fairly well. So essentially, you think of this as a hierarchy chart, right? We have multiple employees as part of an organization, and so on and so forth. Right? And so, the person at the top, let’s say, this is the this is the CEO, right? They, we would actually have we would actually make the Manager ID here nullable. And so the person at the top would not actually have a manager, but everyone else well or you could also make it to where the if if if an employee does not have a manager, then the manager is themself, so you could actually have that as well. So you would have a self referencing loop there. But this organization chart as employee organization chart is really what we represent with just the single table. So a self referencing table is actually a very expressive way to represent this type of information. This similar structure can also be represented in your file system. So if you open up File Explorer in Windows, you have folders and folders within folders, and so on and so forth. How do you actually store the location of a folder? Well, a folder is nothing more than a folder that can be inside of another one, and so on and so forth. So you could represent the parent information with this same self referencing structure. And I’ll show an example of how we might store files here in a little bit. But these are some of the primary variants of relationship types between tables that we may actually represent. And in some following videos, we’ll talk about some more advanced relationships that we can represent between our tables.