Finalize Database Model

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

Lecture description

Design the orders. Discuss about self referencing relationships: category - parent category and enhance the design with product reviews

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 we are getting close to finalize our database design. We started this lecture by designing the orders table which is one of the most important tables in our database. Then we'll go ahead and see a few more examples that will include categories and parent categories and you'll see them for our e-commerce database. Let's move on to the final relationship we have to build the orders. Orders are at the heart of our e-commerce store. They hold very important data related to purchases. So what another has to do is to make the connection between a customer and the items that he bought. A customer can have many orders but an order can belong to only one customer. Otherwise it wouldn't make sense logically. That means that the orders are in a many to one relationship with the customer. Thus the order stable will hold a foreign key with the customer ID will go ahead and I saw a sign and I added to each order to have a primary key. Please note that the order Stabil could have worked with a composite primary key formed by the customer ID and the purchase date. But they are easier to reference if we assign them their own ID next an order could contain one or more items because the customer may buy more than one item. For example he can buy two iPhones one for him and one for his wife so we could add an item say the column to the orders table and store the IDs of the items. But then again that will break the first normal form right. We just have to store one value in one column. This leads to the creation of the orders items table. One last thing to do is to add an unique restriction to the order items. Item id column we don't want the same item to be added to two distinct orders. That would be logically incorrect. So in this way we ensure we have a one to many relationship between the orders and the items and not many to many relationship. Is this clear. You probably are wondering why I didn't just added the foreign key in the items table to reference an order in that way. We would have obtained a one to many relationship without the need of an extra table. If you prefer doing it that way there is nothing wrong. You are completely right. As I told you in a previous slide designing the DBI is both science and art and the designer can make his own choices. I prefer creating the order items table and keep the data on 82 cells in separate tables. It's just a matter of preference. Another lesson that you could get from this example is that you could represent a one to many relationship using an extra table like you would for a many to many relationship and add a unique constraint. That being said lets go ahead and update the db design. Here we are in my sequela workbench. Let's start designing the order of relations first. I'll create one to many relationship between the orders and the customers so let's do that. Great and by the way I did remove this old column. We don't need it anymore and instead of the product I create rename it to ID and assigning it integer value. And this will be the orders primary key. Now what I am going to do next is to create a link between the orders and the items. So lets move the brands away from their so we could create the order items table manually and then create a one to many relationship between the orders and the other items and the same between the items and the order items. But I think it would be easier if we just create the table using the many to many relationship and then rename it to order items and add the unique constraint on the items column and that's it. The design is already. Now let's improve our categories design. Did you notice how every online store has these navigation that allows you to switch categories and see where you are in the store. What we want is to do the same thing for our online store. How do we do that. Can you guess. Of course we need to add a parent column in the Categories table like this. Now the category stable isn't a one to many relationship with itself because a parent category can contain multiple children categories. For example phones and tablets both have the phone and tablets parent category. This type of relationship is also called self referencing relationship. Let's go ahead and create these in our design so here we are in the Workbench. This would be an easy job. I just create a One to Many relationship here on the Categories table and that's it. My siecle added the categories ID foreign key for us I'll just rename it to avoid confusion to patent categories ID and that's it. Let's move on. The last thing that we're going to do is implement a review or stable. What do we want to achieve one or more customers can leave reviews to one or more product. Thus the reviews table acts as an intermediary table between products and customers creating a many to many relationship. The table will also store a rating and review description for each record. The customer ID and the product ID will create the composite primary key for all stable scenes they neatly identify or review. Does this make sense. OK let's go ahead and create that design. Here we are again in my sequel workbench so I'll toss them out to see better the database. OK now what we want is to create that if you're stable which will lead customers and products. Of course we could do that manually but since it's acting as an intermediary table in a many to many relationship I'll just use the many to many relationship BOTTEN here and these created this table for us. With a composite primary key having foreign key as well to the customers and to the product. I'll rename this table as revues and then I'll add the columns orating which will be our number from 1 to 5 eventually. And the description which can be of higher value Okay let's remove that extra column and that's it. We are done. I know that the views higher OK. So now our database design is completed the database is normalized. All the relationships are built and we are done. Don't forget that you can download this design from the resources section of the lecture and in the next lecture We'll talk about how to do forward engineering and generate the actual database. Thanks for watching. See you in a few seconds.