Define Conceptual and Logical Design for an Online Store

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] I and welcome back I hope you manage to install my school workbench without problems and that it's working fine on your computer. In this lecture we will finally start designing our database. For now there is no need to figure out all the details of our database. You just have to think of the conceptual entities to model a real life e-commerce Web site. So are you ready. Let's begin OK. Please. What would be the main entities of our database. Do have some ideas what most certainly will need our customers. Anti-Tea what those well see seems the main purpose is to sell something we will want to create a product entity as well. And we will need something to link these two entities together and represent the sale let's call that anti-Tea orders probably your thought of having a review system or some categories to grow the product. That's very good. Every successful online store has them and we'll add them later as well. But for now let's just think of these three main entities what attributes would you assign them. Let's see what attributes should the customer have. I would assign them an ID which will also be the primary key in an email so that I can contact them. And what that's the name and the address of the customer are very important. We must know where to deliver our products. And I will sign a list of favorite products. I want to allow customers to bookmark their favorite products and maybe send them notifications about these scams or other promotional offers for their favorite products. So the end result would be something like this. OK what about the product attributes. What would you do. While I would add an ID name an About section the price the brand or brand description and the category where the product belongs. What about orders. I added the product and the customer. Since you must know who bought who got the purchase date. So that we know when and the warranty field that will store the number of years the product will be under warranty. Starting from the purchase date. Now if you have some experience with database design you might notice that there is room for improvement. I did that on purpose so that I can show you in the next lecture how to improve this design. So for now let's open my sequel workbench and create these entities. So here we are on my sequel workbench on the diagram tab on the left side as you can see there is a toolbar with several buttons and for now I'll just use this one to create a new entity. So I'll double click it. And as you can see there is a panel open with all the tables properties and let's put this name your customers and add the columns the ID which is an intriguer. By the way we will use several data types. The most important for us will be intriguer to represent numbers Watch out to represent strings and probably we will use float for numbers with that simple point. But for now let's close this. So the idea is of type intriguer is a primary key. Next I'll add the email later please correct the name OK the address will use a larger field for the address of 250 characters. And the list of favorite products is the same let's use a bigger data field and that's it. We created our customers table. There seems to be a mistake here. A column has been added by mistake so I'll just go ahead then remove it. Like these. OK. And now I'll add the product stable the ID which is the primary key. OK. Now I add the name on about section. Once again I use the larger fields or the About section the price I'll use a float data type for the price and then I add the brand. OK. Brand description and the last thing the category that will start the categories to which this product belongs and that's it we have the products table and last thing. The orders as I said earlier will have product I'll choose data type of watch our customer and the product and the customer together can build up the primary key of the Orders table. So at this check here now an order is identified by a composite primary key made up by the customer and the product that he bought later we will see that we can replace these fields and use the IDs and build a one to many relationship. But for now let's just keep it like that. And as I said I'll add the warranty of type and and the purchase date which is date and that's it. We are done with the first part of the design. Now let's move on and apply database normalization rules on our design in the next lecture you'll find what that is and I'll demonstrate how it works on our database models. So for now thanks for watching and seeing the next lecture.