One to Many Relationship

Rares Ilea
A free video tutorial from Rares Ilea
Web Applications Developer | IT Consultant
4.4 instructor rating • 2 courses • 24,700 students

Learn more from the full course

The Complete Database Design & Modeling Beginners Tutorial

Learn Database Design the easy way. Go from simple to complex with a real life example: online store's DB using MySQL.

02:05:14 of on-demand video • Updated April 2019

  • Learn what a database is
  • Learn how databases work and why are they important
  • Learn data modeling and the 3 levels of relational database design
  • Learn what are the steps to create a sound database design
  • Learn what database normalization is and how to apply 1NF, 2NF and 3FN in database design
  • Learn how to build database relationships: one-to-one, many-to-one and many-to-many
  • Understand better every theoretical step by following several concrete examples
  • You will be able to design a relational database from scratch
  • You will be able to answer the most common interview questions about databases
  • You will have a concrete e-commerce database design schema to add to your portofolio
English [Auto] Hi and welcome back in this lecture I show you a few examples of one to many relationships and one to many relationship allows a single record in one table to be related to multiple records in another table. And this is one of the most commonly used type of relationships. So let's see an example from our database. Let's look at the relationship between products and brands. As you can see in the example one or more products belong to our brand. That means that between products and brands there is a many to one relationship. If you look the other way around there is a one to many relationship between brands and products or in other words or brand has many products. Basically the one thing you should note here is that one too many and many to one is the same relationship. Nothing changes in the D-B. It just depends from which side you look at it. OK. That being said before we move to our next example let's update that design in my sequel workbench. Now before I create the relationship between products and brands I want to discuss about the brands primary key so even if we could use the brand name as the primary key in the brand stable because no two brands have the same name I'll add them ID column of type and which would be our number. Why is that. Well in most web applications it is easier to work with IDS which have numbers and on a second thought we do our small size optimization. Imagine that you have 1000 iPhones in your store and for every one of them you have to specify the foreign key to the brand as the word apple instead of a number like one. Obviously that will take much more storage. So I'll go ahead and create the ID column of type and set it as the primary key and I'll remove the brand as the primary key and move the primary key as being the first column and then replace the brand name wait name. Simple as that. OK. And now let's create the relationship. So I'll choose a one to many relationship and click on the product and then on the brand's and that's it the relationship has been created. And as you can see my sequel added by default brands ID foreign key in the products table. Now what do I remove the brand. Column from here since it has been replaced with the brands ID for on key and we are done. Now let's move on with our examples. Imagine the product stable as it is now. We are using some assign IDs for our products. Considering that every product in the world has an unique serial number. Do you think we could use that in place of our ID. Of course we can and probably everything will become better integrated with the cash register. To make up around Tiss for mobile phones the serial number is called I mean a code and you can find it on their backs or on their menu and using it you can verify the phones out in the city. Let's use that as the ID OK. Now let's add a few more products to our table. Imagine you have in your store the iPhone 7 you could as well have 1000. Do you see some data redundancy here. In other words repeating data Well the name tag line description price brand and warranty are repeating for every product. As you can see for the iPhone 7 we should remove that data redundancy from our design. So how could we do that. You may as well imagine that every product we have is actually a physical item with an unique serial number the name description brand etc. are attributes that are the same for each item and represent the product. In our case iPhone 7 they could be extracted in some other table. Think for a moment how would you do that. Ready Let me show you how I would break these data in two tables. One is called products and store genetic data about the product and the other is called item here we store specific item related data. There's no relationship between products and items is a one to many relationship a product can have multiple items or if we look from the other side more items belong to a product. Does this make sense. I want to show you a few more details so that you better understand the advantages. Imagine the iPhone Severn's you want to start the production date where will you do that. In the items table or in the products table. But you right. Not every phone was produced on the same date. So the production date becomes an attribute specific to a product item. Now let's see what other attributes could be placed. How about weight items or products products right. Every item should have the same weight since it's the same product. How about location. Imagine you own multiple stores at different locations. Where would you store the location. You are right on the item. OK. I hope things are clearer now. Let's move on and update the db design. So here we are again and I think the easiest way to do it is to add the items stable at the ID column which will be the primary key. And what that lets the production the of the date Okay that's good. Now let's build the relationship between products and items. So we want many items to belong to a product and that's it. I just move the tables around to be easier to see. So now an item has been linked to the products and as you can see the multiple side of the relationship is here on the items that suit. Let's move on. Let's review the products two products category relationship. Remember we split the tables like this when we discussed about the first normal form in this form of product belongs to one or more categories. So they are in a one to many relationship. Now imagine we add another product in assignats to the same categories. Suddenly we emulate and I say emulate because he's not by the book or many to many relationship between products and categories or product belong to multiple categories and category can have multiple products well tell radically if you keep the categories as simple strings. This could work. But this is not optimal. First of all imagine that you could have 1000 smartphones. That means that in the product category table you'll have to repeat the word smartphones 1000 times and another 1000 times the word phones. Lots of redundancy. Next imagine we add a category description that will look like this. Besides having lots of redundancy. This table is not in the second on will form taking into account that product ID and category for the composite primary key description does not depend on the product. So we'll have to split that table like in this example. What we have accomplished now is preparing the tables for a proper many to many relationship. Join me in the next lecture to see how it works and to see more examples. Thanks. See you in a sec.