Hey guys, in this video, we will start modifying our databases so that we can prepare them to have relationships and foreign keys. Now on the screen, I have the entity relationship diagram for our database that we created on Amazon clone that I'm just going to use this database and modify it a bit just to depict the differences and the intricacies when considering what kind of relationships we need, in which situation. Let's see we are here finishing up this database and we realize that there's a missing table and our table would be products because we have customers and the customer makes an order but what do they order they have to order products so we can just add a new entity here and we label it as products. So I Just took the time and I audited this new entity with all the properties. It's product stable, it has an ID.

It has some product code, a name and a description. Now in the same pain, that's a load of secrets a new entity, we see if we go just a little down a few icons lower than we will see that we actually have some options for some lines to be drawn. And I'm going to walk you through what each option means. We have a one to one kind of relationship where essentially an entity is only allowed to be associated with one entity at a time. So let's say for instance, you run a private school, where only one student is, is assigned to one teacher. So one teacher is hired to deal with one student that would be a one to one relationship.

So if you had to create a database to facilitate that kind of situation, then you need to use a one to one if it was actually Where one customer is only allowed to place one order, then that would be a one to one relationship. And so what you would do is just click on the line, and then you would say, Okay, what table Do I need associate with another table. So if we needed to associate one order with one customer, then having clicked this icon here, I would click the entities. And then you see that it would generate that line with that one to one line. And it would also just put in a new property to facilitate that. Now, we already had something called customer ID.

We could delete it, I mean, it's just a design, we put it in the design, but it is not going to assume that that's what that's why this is there. So just by virtue of us establishing this relationship, he creates the line and it creates a property to facilitate this type of relationship. Now the next type of relationship that is very common Probably the most common one would be the one to many, that would be depicted by one call on end. And we call that notation so to speak, that's relationship notation where it tells you the level of tech or the type of relationship. No a one to many relationship is more like what we have here with customers and orders or either teacher and student relationship than the typical school, you have one teacher and or you have one customer, and this one customer can have many orders.

So a customer can have one order or they can have any number of orders and right now I'm sure any customer on Amazon has many orders. So Amazon would not do very well with a one to one type of relationship between the customers and the Orders table. So the same principle applies. If we were to add this kind of relationship, we will just click the icon and then we will click the team loops that we want to associate first, and then the receive being associated with table. Now let me just explain why the ID or the foreign key gets generated in the we've got generated by seeing by clicking orders. First, we're seeing that this is the side that has a dependency on this side.

Because if you recall from the previous video, an order cannot exist without a customer. So the Orders table for any record to exist in the Orders table, there is a dependency on the Customers table. And once again, instead of repeating the email address and the full name and all the customer detail, each time, there is an order place, we just get a foreign key reference, which is customers underscore ID. And please take note of this naming convention, you can name it anything you want, but there's really a naming convention that is there. That that helps you to know exactly what table this for instance, belongs to. So customers is usually the table name.

And then below that is the ID column. So this naming convention is really good point when it comes to helping us identify foreign keys. But then what I want to drive home really is that by including the ID in the Orders table, the ID is allowed to repeat as many times in the Orders table as needs to be. So if a customer has 10 orders for each of those 10, that customer ID number would be actually the same thing over and over and over and over for the 10 orders associated with that customer. So you can look at it as an association were associating the order on this side with the customer on this side by having the foreign key or you can look at this as a dependency and order needs to have a customer. So we put that dependency in the next type of relationship that is very common is many to many.

And that is depicted by this line here with n to m. And if you just hover over them, they will show you something quickly. It's not very informative, or other than what you're seeing as a notation. But I don't like using any direct relationship between two tables for the many to many. Instead, I introduce what you would call a link or table. Now a situation that would require a link or table or a many to many relationship would be a situation where you have a products table and an Orders table. No your products would be basically your entire catalog of what's available.

This is what's available on Amazon, every item on Amazon goes into this table. Okay, and then every order placed by a customer goes into this table. What we don't have forever details on what is being ordered by the customer. Because for one order you can have many products. And then on the flip side, many products can end up on many orders. So if all of us go on Amazon right now and order pairs of shoes and pick on us some shirts and some pants pairs of pants or and females with their slippers, a lot of those orders will have overlapping products, which means many products will appear in many orders and one order or many orders can have many products.

So then the fact that many can be on either side makes this a many to many relationship. So in order to facilitate this many to many relationship, like I said, I don't like using this relationship indicator that they gave us. So what I do is introduce a new entity, and I'm going to call this one products underscore orders. Now we will put in the properties for production underscore orders. Now, with the linker tables, it is not absolutely necessary to have a primary key, you will see it sometime. So I'm just going to live by what I've been preaching always have a primary key.

So I'm just adding that ID column, and that auto incrementing so that we have a primary key. But the main purpose of the linker table is to store the foreign keys of two different tables that need to be related to each other. But we run the risk of having that repetition. So we put in another table that facilitates repeating the foreign key values. Now, let me explain that again. The scenario is that we have many orders that can have many products, and we have many products that can appear in many orders.

Now if we were to try and relate these tables to each other, then we would have maybe four is having repeating details of products every reversal after all right, and that's what we want to avoid, we don't want orders to have unnecessary information. And the same way we if we were to put orders on the products, then we would have the shirts or that particular product repeating itself for every order that it is associated with. And that is very dangerous. So what we do is introduce a new entity, which is going to associate the products with the orders and I'm calling it products on the store order so that I can easily identify that this is linking these two tables. Alright, and so that's why I keep on stressing the naming conventions that you use on your database objects and know what I will do is add a one to many relationship for the products.

So this table and a one to many relationship from orders to this table. So one product can appear many times, and one order can appear many times. And then for each record that goes in would have a product ID, and an order ID so that we can see that this product is associated with this order. And all we are storing or need to be worried about is the foreign keys for either. So I'm going to go ahead and click my old one colon in icon, and I'm going to start off with the table that has the dependency. So the products on the score orders has a dependency on the products table.

And so we get a product ID introduced and that's our foreign key value from our products table. And I'm going to do the same here. We'll just click that icon again, and I'm going to click Start off with the table with a dependency which is production underscore orders. And I'm telling it that's it also depends on order ID. Okay, so we got a third column being generated here mainly because it really eyes that the table that we're linking this to also has another foreign key in the form of customers underscore ID. So it is telling us that it only gave us a foreign key that would store that value.

Now, while it has the best intentions in doing so, this is also as a result, well, this will result in some unnecessary repetition. The reason for this is what this new rule that we didn't ask for or this new column rather, that we didn't ask for is going to be storing is the customer ID associated with each order. Know Remember, we're storing the product ID, our store in the orders ID. And then this one was given to us to help us to know that we're storing that this customer made this order with this product. And then if we if maybe they made a few orders, then or even one order with Many products, as many times as product ID repeats with the same order ID then the same customer ID is meant to repeat. And the reason I say that this would cause unnecessary repetition is that we already have an association between the Orders table and our customers here.

So we know that any order that with ID 10, for instance, is going to belong to this customer. So wherever 10 appears in this with whatever product ID we already know, by virtue of the fact that we have an association of here who that customer is or we can find out easily so we really don't need to repeat that customer one made order number 10 with 10 products, and then we have customer number one repeating each time for each of those 10 products here again, when customer one is already appearing here. No, I know what I just said was a mouthful. And that is why normally decision is usually a very arduous task. And it requires you to have a keen sense of database integrity and you know, referential integrity. So because I am a practitioner, I could see it from day one.

And I hope that my explaining why we don't need this column makes sense. So I'm just going to remove this column. Because I don't need it. I really just need this to link my orders to my products. And then this Orders table has the details of the order which includes the customer ID if we need to see who made this order. And likewise, the products table has all the details on each product that is in our catalog.

And whatever product is being referenced by its ID here, we can always go and fetch the details in the other table. Now if we were to forward engineer this mock up, it will generate code which would then generate the database with all of these associations and foreign keys built in. I also need to mention that from this design view and or modeling view, this is actually starting of a project. So when we actually go to home and say we want a new model, we start up our project where we can have multiple databases so you get this one by default for then as many as you want, you can add to the screen and have these models and as many er diagrams as you need. And if it is a case where it's an ongoing process, maybe like we're not finished with this database right now.

Then we can always go and save model us and then this will allow us to save an M wb file, which is workbench models file, which we can always reopen and continue our work The database just will not get created until we forward engineers. All right. Okay, so thank you for watching and I will include this model file with the resources for this video. And I trust you enjoyed this lesson. The next lesson we will look about modifying or school database that we already have and starting to add some relationships to it.