eCommerce Database Design: Diagram & Explanation

Поділитися
Вставка
  • Опубліковано 22 гру 2024

КОМЕНТАРІ •

  • @DatabaseStar
    @DatabaseStar  Рік тому +17

    Bonus PDF: I've created a bonus PDF for this database design, which includes the final ERD, a description of each table and column with examples, and the SQL scripts to create all of the tables. Get your copy here: www.databasestar.com/dbdesign/?

    • @Кайчик-ъ4в
      @Кайчик-ъ4в Рік тому

      Thank you very much😍

    • @genagrig4198
      @genagrig4198 Рік тому

      Page not found... Can you fix it please? :(

    • @mka11221
      @mka11221 Рік тому

      Which app/website u used for designing

    • @Dattt122
      @Dattt122 10 місяців тому

      Thank you very much.

    • @DatabaseStar
      @DatabaseStar  2 місяці тому

      @@genagrig4198 It should be working now!

  • @mr.panique
    @mr.panique 10 місяців тому +9

    Your ability to explain is actaully crazy good! Thanks for all this material.

    • @DatabaseStar
      @DatabaseStar  10 місяців тому +1

      Thanks, I'm glad you like the videos.

  • @davosonic60
    @davosonic60 2 роки тому +28

    Thanks for the video! A few comments:
    * I don't think promotions should apply to categories, because sometimes you just want to have a sale for a product or a product_item, not to whole categories.
    * The shipping_address on the order shouldn't be a reference to the address, in case the address changes, the order should have a denormalized version of it.

    • @DatabaseStar
      @DatabaseStar  2 роки тому +8

      Thanks David! Two very good comments.
      Yes, you may not want to have a sale for a whole product category. So it's probably better to have a sale related to a product instead, as you suggested.
      Good point about the address changing. You could use the existing design to cater for addresses for an order where they shouldn't change, but yes having the address on the order is probably better in this situation.

    • @salive8754
      @salive8754 Рік тому

      So how will the denormalized version be.. Please Enlighten me as a beginner

    • @ccgarciab
      @ccgarciab Рік тому +6

      ​@@salive8754 denormalization means bundling data together in a table instead of spreading it across separate tables (bit of an oversimplification). In this case it would mean having the fields required for describing an address in the order table itself, so each order has a copy of the address originally used for placing it, independent of changes done to the actual address record.

    • @philiperiskallaleal6010
      @philiperiskallaleal6010 7 місяців тому

      Dear Salive8754, I understand your explanation; what seem elusive to me yet is how this scenario would happen and how often can one expect to happen in a daily system usage? Once an order has been requested, all its related data (e.g., address) is atomically applied in any report generation. This report then should be immutable, as far as I can understand. Sincerely

  • @musicplayer9353
    @musicplayer9353 3 місяці тому +1

    I was struggling with database until I saw your video. Keep up the good work. I support you.

  • @rohan8758
    @rohan8758 11 місяців тому +3

    Awesome explanation in 16 min video, I would like to thank you teacher.

  • @alexasiricortanayuen7847
    @alexasiricortanayuen7847 2 роки тому +4

    This is great! Thank you!
    just wanna add something about the user_payment_method: You don't store credit card info if you're not PCI DSS compliant.

    • @DatabaseStar
      @DatabaseStar  2 роки тому +1

      No problem!
      Good point about credit card information and being PCI DSS compliant.

  • @jimmyjolling8451
    @jimmyjolling8451 Рік тому +4

    I've been stuck putting the product part of this database structure to use and I've watched this video atleast 3 times and I get the logic. The issue is how does one implement this in practice...
    I'll list my steps on what I thought was the right way:
    1. create a category and have an option to link it to the parent_category. This way we create a hierarchy in this table.
    2. Create a variation ('color') and asign them to the category. ---------------> This is where my first issue is - shouldn't we be able to add color to multiple categories that use them? Like t-shirts, pants, smartphones, etc... That would mean that we need a many to many relationship as multiple categories can have multiple of the attributes and vice versa?
    3. We create a product and the user should fill in all required fields so (product name, description and image) ----------------------------------> here is the second issue: do we store this in the database and then have an option make variations of this product? But then that means if we don't have a variation added that it would be an empty product listing on the listing page. So my second solution was why not make the (create product page) a whole page of all the fields both from the product table and the product_item table?
    4. If we would go with the approach of step 2 and we use many to many that would mean I can query for all the attributes linked to the category that this product has selected and show the options on the page? for example CLOTHING has been selected then we should return color, sizes but if we have SMARTPHONE selected then we return color, storage_capacity as options.
    5. Does the user have to select atleast one of these attributes? And what if they select multiple options that would mean I need to make a new product_item for each selected option? -----------> another issue pops up what if the color dictates which sizes are available? for example a red T-shirt has Small, Medium sizes and a blue T-shirt has Small, Medium and large sizes. Maybe I'm way overthinking this but it's hard to come up with a way that satisfies all these needs...
    On top of all of this I'm using Laravel (PHP) and relationships to "simplify them" but it feels like it makes it even less "readible"...
    Sorry for rambling on but I've been stuck on this for days drawing on paper and writing down logic but whenever I think I got it and start putting it into practice new issues popup...
    I appreciate anyone reading this and feedback or your ideas would be greatly appreciated!

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Thanks for watching and for the in-depth comment!
      Here's my response:
      2 - Yes, that would be a better way to do it I think, as you would avoid duplicate rows that represent the same thing (e.g. "color")
      3 - Yes, that would be an issue. For a Create Product page, having all of the fields would be a good way to do it.
      4 - Yes, you can write a query that does that, so you only see the relevant options.
      5 - Yes, this design assumes there would be one product_item for each different combination you need. Which would also mean, if you don't have some combinations (e.g. there is no Large Red t-shirt) then you don't create a product_item for that.
      Of course, feel free to adjust anything from this design to meet your needs!

  • @MiKu-kx7lw
    @MiKu-kx7lw 2 роки тому +4

    Great work! I just want to create my first ever full stack project and was thinking how to design database. Thank you for this video.

  • @mutalibgozalov7208
    @mutalibgozalov7208 Рік тому +2

    9:05. You've mentioned that product_item table keeps record of e.g the product slim t-shirt with size M and color of black, but those credentials are stored in product_configuration table, which has many to one(respectively) relationship with product_item table, that means one product item with a specific SKU and quantity can have multiple color, size etc. I'm having s little confusion at this part of design, hope someone can help

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Thanks for the comment and example. The product_configuration should have one value for each variation and product. So, product_item 1 has a SKU of 2001, which has two rows in the product_configuration table: one for variation_option 4 and one for variation_option 6. Let's say that variation_option 4 refers to a color of Black, and 6 refers to a size of M. That way you can find all of the attributes of the product.

    • @mutalibgozalov7208
      @mutalibgozalov7208 Рік тому

      @@DatabaseStar How to know how many products of color black and size M are there?

  • @rumble1925
    @rumble1925 Рік тому +4

    ~@13.20 I actually think it's better to not have an extra table for the cart. Just use order as you mentioned and fill it up with values. The cart is in my opinion just an entity that exists in the runtime. The shop then functions almost as a pipeline for filling out the order details.

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good point, I think that could be better too.

    • @rumble1925
      @rumble1925 Рік тому

      @@DatabaseStar Hehe, we'll see if it holds up in the long run, but so far it seems to work ;)

  • @HaxGuru
    @HaxGuru 7 місяців тому +1

    At 11:49, you have created a shopping cart table which only holds its own ID and the related user's ID. Each cart item then stores the cart ID. Would it be a bad practice if, instead of creating a separate table named shopping_cart, each cart_item directly referenced the user ID? Because each user is going to have only one shopping cart anyways?

    • @DatabaseStar
      @DatabaseStar  7 місяців тому +1

      That's a good point and I think it would be a better way of doing it, actually. A user can only have one shopping cart, so your idea can work.

    • @HaxGuru
      @HaxGuru 7 місяців тому

      @@DatabaseStar Thanks! Your videos help me a lot!

  • @simondolapo1567
    @simondolapo1567 2 роки тому +1

    Thanks for the explanation... This is something I'll keep busy at studying

  • @xarix_
    @xarix_ Місяць тому +1

    I know this might be a small thing...but we can have the "qty_in_stock" in the joining table(product_configuration) right?... instead of having it in product_item table....because a a specific variation of a product item could have different available stock...

    • @DatabaseStar
      @DatabaseStar  Місяць тому +1

      Yes, that’s a good idea and you could do that.

  • @factzfactory4956
    @factzfactory4956 6 місяців тому

    Thankyou for this wonderful explanation
    Lot of my doubts automatically got solved as I kept listening your explanation

  • @RANKKY9
    @RANKKY9 Рік тому +3

    I've been really enjoying watching your videos, even though most of the videos I watched were uploaded over a year ago. It's super well explained and therefore easy to grasp.
    Quick question regarding the product_item table:
    It feels like it would make more sense to add "variation_option_id" as an attribute to the product_item table, rather than having both of them be connected in a relation table, especially cause the shopping_cart_item is linking to the product_item and you would need to know which variation you're ordering in your shopping cart when you access it. Maybe I'm thinking about it totally wrong though 😅

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Thanks for the feedback, glad you like the videos!
      Yes, that could work as well, but I think it may not allow for a product to have multiple variation options, such as Colour Red and Size M.
      There have been a lot of comments with suggestions and questions on this video, so I think I should make a follow-up video with this product concept with more examples.

    • @RANKKY9
      @RANKKY9 Рік тому

      @@DatabaseStar Thanks for the clarification, I understand what you mean.
      I would certainly love to watch another video on this concept. I mean there really is no limit to what you could add to a database and I really like how you often point out limitations of your designs and also mention ways to improve on it

  • @xReisk
    @xReisk 3 місяці тому +1

    Thanks for the video! Im currently building my app and really needed some insight since I never touched an Ecommerce website. That said, I would like to know:
    1. Why is the "price" in the "product_item" table? Isnt the best practice to keep track of pricing by assigning it to a separate table where you can track its changes? Like when it started this price, when it ends (for offers?), analytics, etc.
    2. Since Im not a native English speaker or never worked with Ecommerce, are Invoices something we need to keep track of in a Database? Or with the info we have already in this schema we generate the invoices and is something we show to customers out of formality in a image or format as "invoice" as a way to see their "payments".

    • @DatabaseStar
      @DatabaseStar  3 місяці тому +1

      Glad you like the video!
      Here are my responses:
      1. That's a good idea, adding it to a separate table would be a good way to keep track of changes. We could audit the entire product_item row by adding a start date and end date, but your suggestion can work as well.
      2. I assume that we would probably want to store the invoice data somewhere. We can store the price history for a product but we should ensure we can store everything that we captured for a particular order, in case we need to generate the invoice again in the future.

  • @Gr33dyTaRdO
    @Gr33dyTaRdO 6 місяців тому +1

    I really appreciate the time you took to make this video. It's beautifully explained and has a great amount of information! If I could, I would like to ask something in regards to the shopping cart and orders.
    Say a user is signed in and he adds a few products to the shopping cart - now those products will be saved to the database and added to the shopping_cart as I understand. Then when the user decides to checkout those products, will they be copied to the shop_order table through order_line? Or is it that when the user adds products to the shopping_cart, those products should get added to order_line directly too?

    • @DatabaseStar
      @DatabaseStar  6 місяців тому

      Thanks! I'm glad you like the video.
      Good question. I think it's the first scenario: they will be copied to the shop order table. However, another comment suggested that they are kind of the same thing so the shopping cart table could be removed and perhaps an order is just created. Hope that helps.

  • @ranjanadissanayaka5390
    @ranjanadissanayaka5390 Місяць тому +2

    Awsoeme video thank you so much 😊. Please make more database tutorials. Wish you all the best.

    • @DatabaseStar
      @DatabaseStar  Місяць тому

      Thanks, I'm glad you like it. Sure, I have more tutorials coming.

  • @berendvanbeerschot1472
    @berendvanbeerschot1472 Рік тому +1

    Question: About the product_configuration table, does this not store a ton of redundant information? Let's say you sell clothing and thus you have a certain product let's just say a T-shirt, but this T-shirt has variations in many colors and sizes. You also store the material, now you would have assign the material toe very product variaton for (=every available color times every available size).

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Yes, you may have a lot of data in this table, but it would be valid because it matches to real products. Depending on the size of the store, you may have a few hundred or a thousand rows in the table which I think is OK.

    • @berendvanbeerschot1472
      @berendvanbeerschot1472 Рік тому

      @@DatabaseStar Ok this is clear. For my personal alteration I'm thinking about adding a product_type table to store all available (thus available for at least one product) combinations of variation_options, so I would not have to repeat that data and potentially make queries easier. I know this is very case-based but would you recommend this and in/for which cases?

  • @vishalpoddar
    @vishalpoddar 4 місяці тому

    What is the use of the category_id field in the variation table 10:46. Thanks in advance.

    • @DatabaseStar
      @DatabaseStar  4 місяці тому

      This helps to identify which variations can be applied to which categories. For example, size may apply to shoes but not to belts.

  • @DunckingTest
    @DunckingTest Рік тому +1

    How can I determine which variants the customer selected when they place an order for Product Item 1? For instance, if a customer orders a T-shirt with a blue color and size medium, how can I retrieve the specific variants they chose?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      You can use a select query that joins to the related records in other tables.

  • @dead_holicx3127
    @dead_holicx3127 5 місяців тому

    I was designing db for shoe store application this will help for sure. Thanks a lot ❤

  • @adnanasad4674
    @adnanasad4674 4 місяці тому

    Thanks also there could be tags table so to show related product to user while he is checking out, manufactured by table to show electronic company.

    • @DatabaseStar
      @DatabaseStar  4 місяці тому

      That’s true, that could be added.

  • @vishalpoddar
    @vishalpoddar 4 місяці тому

    In product_item table , why is there both SKU and id , can't we use SKU as PK 6:40? Thanks.

    • @DatabaseStar
      @DatabaseStar  4 місяці тому +1

      Good question. The SKU is often something that is shown on the website, or used to identify with a manufacturer, or shown on pricing tags on the product if it's in a physical store. The PK is never shown to a user and is only used for relationships.

  • @abahjosephisrael4459
    @abahjosephisrael4459 10 місяців тому

    Your explanations of all concepts are excellent. Thank you 💝💝

  • @davem8774
    @davem8774 Рік тому +1

    This is a great example to help me build my first relational data tables in Microsoft Dataverse. One question, why do you create a separate table for order_status? What's the advantage compared to just creating a choice attribute in the shop_order table?

    • @DatabaseStar
      @DatabaseStar  Рік тому +2

      Thanks! The reason for a separate order_status is that there is a specific list of order status values, such as "Ordered", "Delivered", "Cancelled". We can add those values into the order status table, and when we select an order status for an order, we select a record from that table. This improves the quality of the data.
      If it's just an attribute in the shop order table, then it would be a free text field, leaving it open to put any kind of value in there.

  • @jam052
    @jam052 Рік тому

    Thank you so much for this video! I have a question though,at 13:08 you mention that this design assumes that the application would convert the shopping cart to shop order and you mention another design which could work with only shop_order table, so how would you go about implementing that design?, which tables would you connect?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Glad you like it!
      Yeah that design could work by connecting the tables that are currently connected to shopping cart to connect to shop order instead. You may want to have some kind of status on the shop_order table to indicate the order hasn't been placed.

  • @DharmaRakshakTadi-he7gx
    @DharmaRakshakTadi-he7gx 2 місяці тому

    Hello,
    I am new to database design and management, and I have a basic question regarding our current database schema.
    1) As the Shop_Order table has a foreign key User_ID that is referencing the Site_User table. Why is there no direct relationship between the tables 'Shop_Order' and 'Site_User'?
    2) Assuming that the relationship between the tables 'Shop_Order' and 'Site_User' is through multiple tables, like 'Shop_Order' is related to 'User_Payment_Method', and 'User_Payment_Method' to 'Site_User' table.
    If this is the case, then in the situation of creating the 'Shop_Order' table in the database, the foreign key 'User_Id' in the table 'Shop_Order' should refer to which table? Is it to 'User_ID' in 'User_Payment_Method' table or 'Site_User' table?
    3) I had feeling that It is better that 'User_Id' foreign key Should directly be related to 'User_Id' in 'Shop_Order' Table, But there is no direct relationship between 'Shop_Order' and 'Site_User'.
    Could anyone kindly clarify if my understanding is correct and let me know if there are any mistakes in my approach?

    • @DatabaseStar
      @DatabaseStar  Місяць тому

      Hi, thanks for the comment! Here are my responses:
      1) Good point, there should be a line from shop_order.user_id to site_user.id. This was an oversight when I created the diagram.
      2) I think given the answer to number 1, this question doesn't apply - the link is to site_user.id.
      3) Yes this understanding is correct.

  • @jaybharti5709
    @jaybharti5709 4 місяці тому +1

    Too good to start your eCommerce website development

  • @MrDieuV
    @MrDieuV Рік тому +1

    Thanks for your video.
    I have a question: what if we want to have multiple images for each product ?
    Should we create a new table "product_images" with three rows 'id', 'url' (as String) and 'product_id' (as foreign key), and remove the 'image' row in product table? Or is it possible to store them directly in the product table (and if yes, how ? Since I think it's not possible to have array as data type in mySQL) ?

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Good question! Yes that's exactly what you can do (a separate table), and I think it's better than trying to add it to the product table.

    • @mylyf6684
      @mylyf6684 Рік тому

      What if we have diffrent images for each variations? And how to manage product without variation?

    • @jimmyjolling8451
      @jimmyjolling8451 Рік тому

      There is a way to store multiple images within one product_img column but it requires some clever string formatting for example:
      In php u can store all the images names that are selected from the admin and concatinate them with a ',' by using a foreach loop on each image uploaded like this 'products/ ' . $img-name . '.' . 'originalextension' . ','
      That will add everything into a string like this: "products/img-name.ext,products/img-name2.ext,products/img-name3.ext,etc...
      You can then when you want to display them separate the string using the explode method and use a foreach to display each of them on the product page. You can easily search for this on the internet but the option is there :)

  • @hoangphan3484
    @hoangphan3484 Рік тому +13

    why the relationship between USER and CART is one-to-many, I just think that it's one-to-one.

    • @DatabaseStar
      @DatabaseStar  Рік тому +12

      That’s a good point. It should be one to one, because a user would only have one cart at a time.

    • @tvkhunmhee4239
      @tvkhunmhee4239 Рік тому

      Umm i think 1 user can have more than 1 cart exp.shoppee or amazon

    • @WillH0724
      @WillH0724 6 місяців тому

      Not necessarily… the application might allow a user to have multiple carts … equaling multiple quotes, one for each cart … the user can then decide which cart to “finalize” before payment … at which time said final cart on payment is then one-one.

    • @ggheemo
      @ggheemo 16 днів тому

      ​That would be impractical because there should be a "save for later" option anyways. ​@@WillH0724

  • @nullentrophy
    @nullentrophy 10 місяців тому

    Hi, great video. I have a question, lets say I create V variation for C category and VO variation option for V variation. What stops me from adding VO variation option to a product entry belonging to some other category than C?

    • @DatabaseStar
      @DatabaseStar  10 місяців тому

      Good question, I don't think there is anything stopping you doing that, which is a drawback of this design.

  • @dMDzn
    @dMDzn 8 місяців тому

    Fantastic video, Ben. This is my first time using a "pivot" table for product variants and has cleared a few things up for me. Would you have any resources on how data retrieval is carried out with the product variants?

    • @DatabaseStar
      @DatabaseStar  8 місяців тому +1

      Thanks! The only thing is my more recent eCommerce Product Design video which includes some SQL on how to do this, which may help with data retrieval: ua-cam.com/video/8bkGKwb29L4/v-deo.html
      Otherwise I don't really have anything.

  • @GeekOverdose
    @GeekOverdose Рік тому +1

    how is integrity maintained between product category for variation/option and product category for product/item? It seems I can compose together a product configuration for a product item that is in a different product category to the variation option chosen

    • @DatabaseStar
      @DatabaseStar  Рік тому

      That's a good point. The design does let you do that at the moment which is not ideal. Perhaps in the application there could be a way to filter the list of available variations for a product when it is added. Or perhaps the data being added to the table could be done via a stored procedure which could include a check for this. Or maybe there's a way to add a table that can capture the valid combinations somehow.

  • @tonyx233
    @tonyx233 Рік тому +1

    Shouldn't shopping_cart_item have a FK to the product_configuration table instead of the product_item table? Since having a FK on the product_item will only add the item without any configuration? Same thing for the order_line FK.

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      That's a great point. It actually should have a link to the product_configuration table, otherwise you'll get the issue you mentioned!

    • @lukystudio009
      @lukystudio009 Рік тому

      Two things come to my mind regarding the problem you pitched out and the solution;
      Firstly, since the slim t-shirt is black and also in size M, this particular item occupies two rows in the product_configuration table, therefore an array of product_configuration FKs would be needed to capture the entire item's configuration in the shopping_cart_item table. Is that right?
      Secondly, can you please further explain the problem you're describing? It does not make sense to me, because if I am not mistaken, to get the item's config, all you have to do is to query the product_configuration table with product_item_id. Therefore any shopping_cart_item and product_config linking seems redundant. Or is there something I am not seeing?
      Or maybe I am completely wrong and just can't comprehend your point correctly lol, my apologies in that case.

  • @MohamadSimo
    @MohamadSimo Рік тому +1

    I really like your video, and honestly i was looking specially for products categories i saw in your ERD, and i have a question why we don't create a separate table. One for Main_Category lets say (fresh foods) and another table for Sub-category like (dairy) and third one like Subsub-category (milk). Is that make the website slower!

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Thanks! Good question about the categories. The reason that we have a single category table instead of 2 or 3 or 4 is because having a single category table is more flexible. You can set up two levels of categories, or 3, or 4, or as many as you want. A category just links to its parent category.
      It also makes it easier to change the categories if needed. For example, if you split an Electronics category into TVs and Computers, then it's easier to do this in a single table. If there are multiple tables, you would have to move data between tables and update links from other tables.

    • @MohamadSimo
      @MohamadSimo Рік тому

      thanks for explaining :) @@DatabaseStar

  • @ogunacg3387
    @ogunacg3387 11 місяців тому +1

    I have a question about shoping_car_item and product_item; shouldn't they be related one to one , I mean each product Item can be added to Shopping Cart ITem for the sake of common sense but not more than one diffrent product that's why we keep the quantiity of product inside modal.

    • @DatabaseStar
      @DatabaseStar  11 місяців тому

      Good question. Yes, you're right, it should be one-to-one. It is a PK-FK relationship but there should only be one product_item_id for each shopping_cart_item.

    • @ogunacg3387
      @ogunacg3387 11 місяців тому

      @DatabaseStar can you elaborate your last sentence a little bit more? What do you mean exactly? You are saying, hypothetically, I have a phone for a product item, and it can be added to different shopping cart item.

  • @grwmix
    @grwmix 10 місяців тому

    Hello. First of all, I really appreciate the video; it really is informative and concise. However, I would like to ask a question. If my service only has size as variation, and I decide to use the method from your video about Product Attributes (Product Table, Size Table, and Product Entry Table). How would I go about the cart-item and order-line step? Should I add both Product ID and Size ID as foreign keys for the Cart Item Table? Or should I add a primary key to Product Entry table and add it to the Cart Item Table? I am looking forward to the answer. Thank you.

    • @DatabaseStar
      @DatabaseStar  9 місяців тому

      Thanks! I think this design will work the same regardless of the number of variations you have. You could stick with the approach in this video (adding a product_item to the shopping_cart_item table), or you could add the size_id to the cart table. I think either could work.

  • @srinivasoguri3299
    @srinivasoguri3299 Рік тому +1

    How can I make sure there is no duplicate addresses ? As and when I enter a address it will generate a new key for that address and add it to addresses table with new key and add the same address key to user_address relation.

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good question. You can avoid duplicate addresses by identifying which columns define what a duplicate is (e.g. address 1, address 2, city, country), and then creating a Unique Constraint on those columns. When a row is inserted into the table, but it has the same combination of these values as an existing row, then you'll get an error such as "unique constraint violated".

  • @ktm00072
    @ktm00072 Рік тому +1

    Could you update the ERD png taking into considerations of some of the issues earlier comments' mentioned?

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Good idea! I can update the design and add another PNG file with the comments.

    • @ktm00072
      @ktm00072 Рік тому

      @@DatabaseStar Thanks for your effort. I am watching your database design playlist daily. It's great fortune for newbie like me. Your updated ERD will give us an idea about how to make decision when some requirements may change along the way. Please, do it for other ERD png as well that would be great help.

  • @gsarnaudov
    @gsarnaudov 2 роки тому +1

    Nicely done, but how can we assure having one unique variation per product item? Let's say we have color (red, blue) and size (S, M) variations. From what I see in the video nothing is stopping us from associating Red-S and Red-M with a single product item. What I can think of is adding the variation_id also into the pivot and create a Unique key with both the product_item and variation_id. What would you do to handle this situation? Thanks!

    • @DatabaseStar
      @DatabaseStar  2 роки тому

      That's true, there's nothing stopping this happening in the current design. Adding a unique key/unique constraint would enforce this, as you suggested. So if someone adds a second row with the same combination, then they would get an error.

  • @AbdullahSajjad-p4p
    @AbdullahSajjad-p4p 9 місяців тому

    Awesome ERD. Thanks a lot sharing.
    I just had a question about maintaining history. Suppose it’s been two months since our eCommerce store is running smoothly and a user A has ordered 6 times and is a happy customer. Now he does change his address I mean edits his address but we have previously delivered orders on this address so when user then goes through its history, would not it see changed address instead of one where address was actually placed?

    • @DatabaseStar
      @DatabaseStar  9 місяців тому

      Thanks!
      Good question. That can be covered by a user adding a new address for their future orders. Old orders would be at their original address, and any new orders are sent to their new address. We store both because of the user_address joining table.

  • @norex1298
    @norex1298 Рік тому +1

    Does anyone know how to do filtering of product items by their variation and variation options? I'm really stuck with this problem right know, Java 17 + Spring Data JPA, but still can't find a valid way to perform adequate filtering query. I will be very grateful for advice

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Could you join to these tables and use a where clause? Unless I am missing something...

    • @TienVu-op8xt
      @TienVu-op8xt 8 місяців тому

      up

  • @nigelnyakudya2262
    @nigelnyakudya2262 Рік тому +1

    Great work brother, in case i want to a multi vendor ecommerce what other tables to i require n relationships to this existing one?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      I assume a vendor = store or website? For example, one website/vendor to sell books, another to sell mens clothes? If so, you could have a vendor table linked to maybe the product table so you can link products to the vendor you want to allocate them to.

  • @houseofmalik9944
    @houseofmalik9944 10 місяців тому +1

    great content, Im interested to see how search products would work in an ecommerce website i.e. given a keyword how would I retrieve the related products in the least time from datablase. Also a video on how to index and what to index in these tables for best performance would be helpful. I don't see a whole lot of content on youtube on how to implement search for an ecommerce website.

    • @DatabaseStar
      @DatabaseStar  10 місяців тому +1

      Good question! For searching, I think you may have a SELECT query that would have a series of OR statements. For example, searching for "green":
      WHERE product_name LIKE '%green%'
      OR product_description LIKE '%green%'
      OR attribute_option_name LIKE '%green%'...
      There may be better ways to do this. One issue that comes to mind with this is that performing searches like this (wildcard searches) can be quite slow. There may be database features that you can use for searching but I'm not that familiar with them.
      I can look into it and create a video for it.

    • @houseofmalik9944
      @houseofmalik9944 10 місяців тому

      @@DatabaseStar appreciate your response yes if there is a faster way to achieve this a video will be great help

  • @dagty11
    @dagty11 2 роки тому +2

    Great video! I do have a question about your shopping_cart relation to user_id. Shouldnt it be a one to one relationship, since each user will have one shopping cart that belongs to them, and the user will belong to one shopping cart per account? Thank you!

    • @DatabaseStar
      @DatabaseStar  2 роки тому +2

      Thanks! Good question. Yes, you're right, it should be one to one because there could only be one shopping cart per user.

    • @dagty11
      @dagty11 2 роки тому

      @@DatabaseStar Thank you for the quick response. Another question I have is about the self join for the product_category table, is there any docs on where I can learn how to implement the self join relationship if I were using Flask/SQLAlchemy for my backed? Its an incredibly interesting topic being able to stack the categories that way to avoid creating unnecessary tables!

  • @_TheOldMan_
    @_TheOldMan_ Рік тому +1

    Orderlines would likely need to be able to exist after a product was deleted, meaning that any data you might need about the product, should be stored on the order item. (Assuming you're not implementing some form of softdeletes).
    Also not crazy about the way you're storing categories, at first glance referencing parent is fine, but i suspect you'll quickly fins youself fetching all categories just to rebuild your "tree".

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good point about orderlines and deleting records. I would recommend using soft deletes in this instance, so you don't lose any information for related records as you have suggested.
      Good point about the categories. It's possible to rebuild the tree of categories using a concept called a "hierarchical query". Each SQL vendor has syntax to get this kind of result.

  • @abdullahAboelkhair
    @abdullahAboelkhair 9 місяців тому

    Thank you sir.
    I was wondering how to implement the payment tables if I am using an external payment gateway 🤔

    • @DatabaseStar
      @DatabaseStar  9 місяців тому +1

      How to implement them in the application or the database design? In the database design you could follow a similar process and add the tables you needed.

  • @crazyboytmpas9065
    @crazyboytmpas9065 17 днів тому

    Why do you think we don't connect ShopCart and Order directly? Wouldn't it make more sense to order directly from the cart? please helpp:(

    • @DatabaseStar
      @DatabaseStar  13 днів тому +1

      Good point, I think other comments have mentioned something similar too. A shopping cart could be a kind of order that hasn't yet been placed, so it could make sense to combine them or connect them.

  • @hakobhakobyan4689
    @hakobhakobyan4689 Рік тому +3

    I think our design could become simpler (having less tables and relations) by using Enums too, for example instead of having a order_status as a separate table, and a foreign key order_status in shop_order table we would have had the order_status as an enumerated type. Same for payement_type table.

    • @DatabaseStar
      @DatabaseStar  Рік тому +7

      Good point. We could have enums for those selections. However, enums are not supported by all databases. Enums are also not easy to update if you want to add or remove or rename values that are applicable (using a lookup table is much easier). Also if you want to add more context or description to each item, you can't do that with an enum.

  • @andersonoki2172
    @andersonoki2172 2 роки тому +1

    Great video, Relational DB can be tricky has been working on a project for about a month. Each design i come up with doesn't seem to fit. How long does it take to design a complex DB diagram

    • @DatabaseStar
      @DatabaseStar  2 роки тому +3

      Thanks! It can take anywhere from a few hours to a few weeks I think, depending on how well you know the data and requirements and how complex it is. It's something that can evolve as you start using it, I think,

  • @philiperiskallaleal6010
    @philiperiskallaleal6010 7 місяців тому +1

    Dear DatabaseStar, thank you for the vídeo. Amazing work; truly well explained. During your 9th minute and 50 seconds, I got wondering why you chose to define the cost of the product in the product_item table, instead of the of the product_configuration one. As you well pointed out previous in the video, a product's cost may well vary depending on the options selected. Extending the problem, an Item's image may also vary depending on it's configuration (e.g. size, color, fabric, etc.). Therefore, one could well assume that the property Item_image should also be placed in the product_configuration table. Please let me know your thoughts on this regard. I thank you for your time and I hope to hear from you soon. Sincerely,

    • @DatabaseStar
      @DatabaseStar  7 місяців тому

      Hi, thanks for the question! The reason the price is in the product_item table is because the record in this table represents a unique item that is sold: it has a SKU, a quantity in stock, and a price. A web page might show the data from the product table, and if a user selects different colours or sizes, then different product_items are shown and then different prices and SKUs.
      The product_configuration contains many records for each product_item. It's a list of all of the options that apply to the product.
      Regarding the image: the design I have isn't that good. It should be in a separate table as product_items could have multiple images.
      Hope that makes sense!

  • @HaniHani-lu6hw
    @HaniHani-lu6hw Рік тому

    Great work Thankyou , i have a question please, relationship between shop_order and user_payment_Method . so why (many )relation is in the side user_Payment_Method not in side of shop_order? Is relationship reversed? thank you ...

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good point! I believe the arrow is facing the wrong way on the diagram. The three-line part should be on the FK shop_order.payment_method_id, and the single line with the line crossing it should be on the user_payment_method.id.

  • @sanalm2349
    @sanalm2349 Рік тому

    I have a doubt. If am about sell a shirt it can have multiple Variant combinations. Right? There can be different color options as well as different sizes. How can i handle the combination of these with this DB structure?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Yeah that's right. This structure is designed for that, you'll have different variation options and product items to cater for it.

    • @sanalm2349
      @sanalm2349 Рік тому

      @@DatabaseStar I suppose my question was not straightforward. I'll try to explain with the same example as above. Consider I am selling a shirt. The product details will be persisted in the "Product" table. This product can have a category structure like Clothing -> Topwear -> Men. This can be achieved with the help of the "product_category" table. This shirt can have different variations of colours. So I will add the "COLOUR" entry in the "variation" table and its values like "Green", "Blue", and "White" in the "variation_option" table. New product items for each of these variations will be added in the "Product_item" table and it will be linked to the "variation_option" table with the help of the "product_configuration" table. I understood till this. and everything works.
      But, under the blue shirt product item, there can be size variations, right? it can have "S", "M", "L", "XL", "XXL" and other similar things. How can I add these variations to the table? Or is my first part wrong??
      Basically, what I need is I want to store a set of shirts in different colours and sizes. How can I properly store this?

  • @kalemamark1216
    @kalemamark1216 Рік тому

    Thanks for the video it has been of great help. I'd like to know whether the entities here are the exact tables to be created in the database. if not all of them, how can i identify the required ones

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      You're welcome! Yes, these would be the exact tables I would create. In this design I suggest creating all of these tables to achieve the features that are described in the video. But this is just one way of doing it - there are many comments on the video suggesting some different ways, and even better ways than my design

  • @Shkkmj6868
    @Shkkmj6868 3 місяці тому

    You are a great explainer. Thanks a lot.

  • @franciscomarin5191
    @franciscomarin5191 Рік тому

    Awesome, I just found this channel and its pretty helpful, thank you! just subscribed

  • @mylyf6684
    @mylyf6684 Рік тому

    How to handle product without variation and product with variations in this case??
    I will have diffrent names if variations are there and stock, sku, etc need to be on both table product and product_item?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Yes, if you want different names you could have different product items.

  • @OsteenOmega-kr1kd
    @OsteenOmega-kr1kd 7 місяців тому

    Hello sir. Is the relationship between user_payment_method and shop_order really ok? I just can't understand how user payment method table has a primary key which somehow is on the many side of the relationship

    • @DatabaseStar
      @DatabaseStar  7 місяців тому

      Good point! The PK-FK is correct, but the line is incorrect (it should be around the other way): there should be one user_payment_method for each shop_order.

  • @chris_ea
    @chris_ea 3 місяці тому

    Thanks! What diagram tool are you using?

  • @luizetulio4888
    @luizetulio4888 Рік тому +2

    Hi, this is great video. Thank you for sharing your knowledge.
    I was wondering how would be the approach for subcategories in this design, considering it may have many subcategories for many categories. Ex. 01 dematting Brush / (in dogs, in cats, in rabbits as subcategory) / (in pets or in accessories as category)

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Thanks! I would suggest using the product_category for subcategories. The table is related to itself, so a category of "dematting brush" could have a parent category of "dogs", and that would have a parent categry or "pets" for example.

  • @asifalikhan5281
    @asifalikhan5281 18 днів тому

    how do you make payment_method_id as foreign key in shop_order as there is Many to 1 relationship between user_payment_method and shop_order

    • @DatabaseStar
      @DatabaseStar  18 днів тому +1

      There is one payment method for a shop order, but a payment method can apply to many shop orders. The foreign key in shop order refers to the chosen payment method.
      However, the diagram may be confusing as the arrow is around the wrong way: the "fork" part should be on the shop_order.payment_method_id and not the user_payment_method.id field. A few other people pointed this out in the comments after I published the video.

    • @asifalikhan5281
      @asifalikhan5281 13 днів тому

      @@DatabaseStar thanks

  • @GeekOverdose
    @GeekOverdose Рік тому

    for the product variation: there is 'name' and 'value'. which makes sense. but. how to know the data type of the 'value'? because it can be a number, text, enum, etc

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good question. Allowing this kind of flexibility with product variations (which is like an "entity attribute value" design) has adrawback of enforcing data types. You could have specific columns for attributes but the attributes would need to be added to the table as needed.

  • @headonhill5107
    @headonhill5107 Рік тому

    suppose we change the details of a product like price and name etc. It will reflect in order history too. how will we resolve that?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good question. You could prevent "editing" of products, and when changes are needed, a new product row is created in the table.

    • @Pato-y3g
      @Pato-y3g Рік тому

      ​@@DatabaseStar I also had the same doubt, could you elaborate further? Like... creating a new product line in the table, and the current one becoming unavailable for purchase? I thought about the following scenario: the user makes a purchase, and later checks the history, but the prices, names, or any information related to the product changes. How would we maintain the integrity of this history while respecting relational database best practices?

  • @toyly2820
    @toyly2820 2 роки тому

    This is awesome explanation and really rare diagram picture). i came here after one was explaining to save variation_options in a string like yellow-XL and make it unique by sorting alphabeticly.

  • @iamsuchung
    @iamsuchung 4 місяці тому

    Hi , thanks for the video
    I have a question. What software or application or website your are using for designing database? I mean whats the name of this black and white database designing tool you are using in this video?

    • @DatabaseStar
      @DatabaseStar  4 місяці тому

      Thanks! I'm using a tool called LucidChart.

  • @franciscandelaria3535
    @franciscandelaria3535 Рік тому

    Hello, thank you for this video. It helps me a lot. However, I've noticed that the entity "shop_order" and "site_user" entities have no connection.

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Glad you like the video! What do you mean that they have no connection? I've checked the ERD (linked in the description) and there is connections or relationships to other tables.

  • @amitkumar-vy1ib
    @amitkumar-vy1ib 8 місяців тому

    Hey, I think Price should be present inside product_configuration.

    • @DatabaseStar
      @DatabaseStar  8 місяців тому

      It could go in that table, but there would be multiple product_item entries and therefore multiple prices for the same thing.

  • @onyekachifrancis4572
    @onyekachifrancis4572 2 роки тому +2

    hi brother wonderful work, i am just getting started wit mysql

  • @lyricass7810
    @lyricass7810 10 місяців тому +3

    very nice explanation sir

  • @shahdkhader4389
    @shahdkhader4389 Рік тому

    thanks for great vedio, can we put the variation option table as an attribute to the variation table?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Yes, you could, but there might be duplicated data in the table.

  • @jorgesisco981
    @jorgesisco981 Рік тому

    Why did you create a FK from the PK inside the same table? (product_category)

    • @DatabaseStar
      @DatabaseStar  Рік тому +4

      Good question. This allows us to do something called a "self join", which is where the parent of one record is another record in the same table. It means that a category (e.g. Electronics) can have a subcategory (e.g. TVs) , and both the Electronics category and TVs subcategory are records in the product_category table. It also means we can have many levels of categories, all in the one table, without the need to create multiple tables.

    • @jorgesisco981
      @jorgesisco981 Рік тому

      @@DatabaseStar Wowww thanks a lot for your response!

  • @halahal-hussien4781
    @halahal-hussien4781 Рік тому

    This is so helpful ..Many Many thanks to you!! ..God Bless U

  • @jamesopoku8049
    @jamesopoku8049 2 роки тому

    Hi sir, good work, please may know the software you use for creating the ERD ? thanks

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Thanks! I'm using a tool called LucidChart

  • @allisfehr
    @allisfehr 2 роки тому

    Are there standards about having certain things in a single database? For instance is it acceptable to have payroll and other accounting info within the same database that contains e-commerce & user data tables?(with encryption of course)

    • @DatabaseStar
      @DatabaseStar  2 роки тому

      Good question. There may be some standards but I'm not aware of any. Some things to consider are which applications are using the database (e.g. if there is one payroll system then maybe that has its own database), which teams are working on the applications, how often they may change.

  • @anmarm.9487
    @anmarm.9487 2 роки тому

    Really informative. Thanks. i cant make up how SQL queries will be to select products and its variance ???

    • @DatabaseStar
      @DatabaseStar  2 роки тому +1

      Thanks! You would select the columns you need from the product and variations tables, and join between them. You can filter on values in any of the columns, for example if you only want to see variations for one product.

  • @mhmdshaaban
    @mhmdshaaban Рік тому

    What I really don't understand is how addresses can be shared with multiple users? It prevents data duplication, but what if a user edits his address. Will that effect others users addresses?

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Good question. If a user needs to edit their address, they will change their address on a screen, but perhaps in the database creates a link to an existing address record. It shouldn't affect other user's addresses.

    • @vasilesambor7473
      @vasilesambor7473 3 місяці тому

      I think the address should not be in many to many relation with user, but in 1 to many i.e. one user can have multiple addreses, that mean that address table should have user_id as foreign key. Thats how I would define it.

  • @enricoroselino7557
    @enricoroselino7557 Рік тому

    how about using UUID ? should its inside user table?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Do you mean as an alternative to the id column? If so then yeah that can work.

  • @PhantasyAI0
    @PhantasyAI0 8 місяців тому

    can you do one for a SaaS e-commerce schema, with multi tenancy in mind? something like shopify please.

    • @DatabaseStar
      @DatabaseStar  8 місяців тому

      When you say multi tenancy, do you mean multiple customers or accounts with their own dedicated area of the database?

  • @allisfehr
    @allisfehr 2 роки тому

    How common is it to create your own e-commerce platform by creating a database design, integrating shopping cart workflows & payment methods, etc versus using woo-commerce, shopify, etc?

    • @allisfehr
      @allisfehr 2 роки тому

      Also, do you know if there are platforms that give users read access to database tables?

    • @DatabaseStar
      @DatabaseStar  2 роки тому +1

      Good question! I don't think it would be that common at all. If you want to start a business to sell things on an online store, it's usually better to use a proven solution such as Shopify or WooCommerce like you said.

    • @DatabaseStar
      @DatabaseStar  2 роки тому +1

      I don't know, actually. I'm not sure how the platforms allow access to the database tables. I assume it would be in their documentation.

  • @saybers-4516
    @saybers-4516 Рік тому

    Why not storing the city the same as the country in seperate table ?, bth i didn't understand the pros of doing the country in seperate table , and in the product item when you specefiy configuration for it the table has variation_option_id which refers to the variation_option table which require a variationId which require categoryId so this way the options are only for the category and i cant set an option for one product item

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      The reason the country is in a separate table is because the countries are a defined list of values. There are about 200 countries in the world, and they don't change that often (they do change but not often). For cities, they may not change that often, but there is a larger number of cities in the world, and how do we know what counts as a city?

    • @saybers-4516
      @saybers-4516 Рік тому

      @@DatabaseStar Thank you great video, I'm creating a fullstack ecommerce from this video, great job.

  • @marknaas1938
    @marknaas1938 Рік тому

    What do you do for department and category images?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      If you want images for those, you could add an image column to those tables and add the image there.

  • @trishulcurtis1810
    @trishulcurtis1810 10 місяців тому

    Excellent Video!

  • @andrewnguemnang2757
    @andrewnguemnang2757 Рік тому +1

    How can i query a product with variation

    • @DatabaseStar
      @DatabaseStar  Рік тому

      You could select from the two tables using a join.

  • @prateekmahajan8680
    @prateekmahajan8680 2 роки тому

    By the way what tool has been used here to draw ER diagram?
    I. E Which online tool is the one used in above video?
    @databaseStar

    • @DatabaseStar
      @DatabaseStar  2 роки тому

      I use LucidChart to design these diagrams.

  • @sjzz
    @sjzz Рік тому

    Can this be used for NoSql or Non relational database? Like MongoDB ??

    • @DatabaseStar
      @DatabaseStar  Рік тому +1

      Yes, the same concepts could apply, though I'm not sure about the process to create a database with noSQL.

  • @christmex4540
    @christmex4540 Рік тому

    But i have a question, how can we know, every uniform stock where the size is m? or size is L, how many m and how many L. thaks

    • @DatabaseStar
      @DatabaseStar  Рік тому

      You can query the product and related tables for the variation where the size is M

  • @triskominfoss
    @triskominfoss 5 місяців тому

    Thanks for all this material

  • @circumz
    @circumz 2 роки тому +1

    Excellent work sir

  • @maneeshgupta2448
    @maneeshgupta2448 7 місяців тому

    What is the name of the software that you are using to create this ER diagram?

  • @jimmyhung6087
    @jimmyhung6087 2 роки тому +1

    where is the line of shop_order -> user_id line ?

    • @DatabaseStar
      @DatabaseStar  2 роки тому +1

      Ah you're right, there should be a line there!

  • @nibrasmuhammed5105
    @nibrasmuhammed5105 Рік тому

    you've missed product images. you only designed a column in database for image. a product can have many images. using the above design, we can only have a single image for a product. how will you address this issue?

    • @DatabaseStar
      @DatabaseStar  Рік тому

      Good point! Multiple product images would be common with eCommerce websites. I would probably add a new table related to the product table that contains images. This new table would have an ID for the image, a field for the image, and a product ID to link back to the product as a foreign key.

  • @zhh174
    @zhh174 4 місяці тому

    It would be very nice if you can make a similar video for multi vendor ecommerce database design?

  • @OmarAhmed-zz7vw
    @OmarAhmed-zz7vw 5 місяців тому

    what is the website you used to draw erd

    • @DatabaseStar
      @DatabaseStar  5 місяців тому

      I use a tool called LucidChart.

  • @AbdulRehmanRasool
    @AbdulRehmanRasool 8 місяців тому

    how the product will have multiple images?

    • @DatabaseStar
      @DatabaseStar  8 місяців тому

      This design only has one image per product, but you could expand it to use a separate product_image table to allow for multiple images for a single product.

  • @mutalibgozalov7208
    @mutalibgozalov7208 Рік тому

    Amazing explanation, Thanks a lot

    • @DatabaseStar
      @DatabaseStar  Рік тому

      You are welcome!

    • @mutalibgozalov7208
      @mutalibgozalov7208 Рік тому

      @@DatabaseStar 9:05. You've mentioned that product_item table keeps record of e.g the product slim t-shirt with size M and color of black, but those credentials are stored in product_configuration table, which has many to one(respectively) relationship with product_item table, that means one product item with a specific SKU and quantity can have multiple color, size etc. I'm having s little confusion at this part of design, would you help me understand please

  • @carthai4590
    @carthai4590 6 місяців тому +1

    you're the best

  • @tonyx233
    @tonyx233 Рік тому

    Very helpful. Well Done!

  • @nmqbrightray
    @nmqbrightray Рік тому

    Thank you, it's very helpful

  • @AbhishekSingh-tf6sx
    @AbhishekSingh-tf6sx Рік тому +1

    Awesome!