eCommerce Database Design: Diagram & Explanation

[@DatabaseStar]

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в]

Thank you very much😍

[@genagrig4198]

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

[@mka11221]

Which app/website u used for designing

[@Dattt122]

Thank you very much.

[@DatabaseStar]

@@genagrig4198 It should be working now!

[@mr.panique]

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

[@DatabaseStar]

Thanks, I'm glad you like the videos.

[@musicplayer9353]

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

[@DatabaseStar]

Glad it helped!

[@davosonic60]

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]

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]

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

[@ccgarciab]

​@@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]

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

[@jimmyjolling8451]

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]

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!

[@rohan8758]

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

[@DatabaseStar]

Glad it was helpful!

[@alexasiricortanayuen7847]

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]

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

[@MiKu-kx7lw]

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.

[@DatabaseStar]

Glad to hear you enjoyed it!

[@rumble1925]

~@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]

Good point, I think that could be better too.

[@rumble1925]

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

[@ranjanadissanayaka5390]

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

[@DatabaseStar]

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

[@hoangphan3484]

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

[@DatabaseStar]

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

[@tvkhunmhee4239]

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

[@WillH0724]

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]

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

[@factzfactory4956]

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

[@DatabaseStar]

Glad to hear it!

[@xarix_]

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]

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

[@mutalibgozalov7208]

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]

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]

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

[@adnanasad4674]

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]

That’s true, that could be added.

[@simondolapo1567]

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

[@DatabaseStar]

Glad it was helpful!

[@jaybharti5709]

Too good to start your eCommerce website development

[@DatabaseStar]

Thanks!

[@HaxGuru]

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]

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]

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

[@vishalpoddar]

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

[@DatabaseStar]

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

[@Gr33dyTaRdO]

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]

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.

[@nigelnyakudya2262]

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]

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.

[@DunckingTest]

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]

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

[@xReisk]

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]

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.

[@dead_holicx3127]

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

[@DatabaseStar]

Glad it was helpful!

[@RANKKY9]

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]

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]

@@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

[@nullentrophy]

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]

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

[@dMDzn]

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]

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: kzbin.info/www/bejne/bpPOeH6tl5dsgpY Otherwise I don't really have anything.

[@DharmaRakshakTadi-he7gx]

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]

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.

[@AbdullahSajjad-p4p]

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]

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.

[@lyricass7810]

very nice explanation sir

[@DatabaseStar]

Thanks!

[@abdullahAboelkhair]

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

[@DatabaseStar]

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.

[@chris_ea]

Thanks! What diagram tool are you using?

[@DatabaseStar]

I’m using Lucidchart

[@onyekachifrancis4572]

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

[@DatabaseStar]

Good to hear!

[@_TheOldMan_]

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]

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.

[@abahjosephisrael4459]

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

[@DatabaseStar]

You're most welcome!

[@houseofmalik9944]

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]

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]

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

[@MohamadSimo]

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]

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]

thanks for explaining :) @@DatabaseStar

[@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]

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

[@vishalpoddar]

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

[@DatabaseStar]

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.

[@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]

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.

[@berendvanbeerschot1472]

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]

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]

@@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?

[@norex1298]

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]

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

[@TienVu-op8xt]

up

[@philiperiskallaleal6010]

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]

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!

[@GeekOverdose]

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]

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.

[@srinivasoguri3299]

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]

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".

[@shahdkhader4389]

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

[@DatabaseStar]

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

[@MrDieuV]

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]

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]

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

[@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 :)

[@davem8774]

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]

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.

[@grwmix]

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]

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.

[@iamsuchung]

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]

Thanks! I'm using a tool called LucidChart.

[@Shkkmj6868]

You are a great explainer. Thanks a lot.

[@DatabaseStar]

You're welcome

[@zhh174]

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

[@DatabaseStar]

Good idea!

[@ktm00072]

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

[@DatabaseStar]

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

[@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.

[@andrewnguemnang2757]

How can i query a product with variation

[@DatabaseStar]

You could select from the two tables using a join.

[@asifalikhan5281]

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]

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]

@@DatabaseStar thanks

[@crazyboytmpas9065]

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]

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.

[@amitkumar-vy1ib]

Hey, I think Price should be present inside product_configuration.

[@DatabaseStar]

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

[@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]

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

[@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?

[@hakobhakobyan4689]

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]

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]

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]

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,

[@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]

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

[@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?

[@ogunacg3387]

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]

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]

@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.

[@PhantasyAI0]

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

[@DatabaseStar]

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

[@franciscomarin5191]

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

[@DatabaseStar]

Awesome, thank you!

[@toyly2820]

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.

[@DatabaseStar]

Thanks, glad you like it!

[@dagty11]

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]

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

[@dagty11]

@@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!

[@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]

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

[@luizetulio4888]

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]

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.

[@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]

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.

[@OsteenOmega-kr1kd]

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]

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.

[@marknaas1938]

What do you do for department and category images?

[@DatabaseStar]

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

[@tonyx233]

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]

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]

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.

[@halahal-hussien4781]

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

[@DatabaseStar]

Glad it was helpful!

[@OmarAhmed-zz7vw]

what is the website you used to draw erd

[@DatabaseStar]

I use a tool called LucidChart.

[@gsarnaudov]

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]

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.

[@circumz]

Excellent work sir

[@DatabaseStar]

Thanks!

[@jamesopoku8049]

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

[@DatabaseStar]

Thanks! I'm using a tool called LucidChart

[@anmarm.9487]

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

[@DatabaseStar]

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.

[@enricoroselino7557]

how about using UUID ? should its inside user table?

[@DatabaseStar]

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

[@alper_mulayim]

hello database star and subscribers to amazing channel, what is your opinion and best practice for variation design for product in cases? Such as what is your opinion to keep data in json object in table for product? Could you please compare this solutions pros and cons? And your preferred simple solution ideas? Thanks wishing best!

[@DatabaseStar]

Good question. I've just published a video on this today, which hopefully answers your question. A JSON model can work as it's more flexible, I think.

[@triskominfoss]

Thanks for all this material

[@DatabaseStar]

No problem!

[@maneeshgupta2448]

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

[@DatabaseStar]

I'm using LucidChart.

[@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]

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.

[@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]

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.

[@jorgesisco981]

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

[@DatabaseStar]

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]

@@DatabaseStar Wowww thanks a lot for your response!

[@sjzz]

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

[@DatabaseStar]

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

[@mutalibgozalov7208]

Amazing explanation, Thanks a lot

[@DatabaseStar]

You are welcome!

[@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

[@trishulcurtis1810]

Excellent Video!

[@DatabaseStar]

Thanks!

[@AbdulRehmanRasool]

how the product will have multiple images?

[@DatabaseStar]

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.

[@allisfehr]

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]

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.

[@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]

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]

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

[@nmqbrightray]

Thank you, it's very helpful

[@DatabaseStar]

Glad it was helpful!

[@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]

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]

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.

[@jimmyhung6087]

where is the line of shop_order -> user_id line ?

[@DatabaseStar]

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

[@ZeeshanAli-m9g]

please can you add vouchers in this scenario for me ? or create a separate detailed video for vouchers ?

[@DatabaseStar]

Sure, how do you think vouchers would apply to this design? What are the rules you need? Some ideas: do vouchers have an end date or are they open ended? Do they apply to certain products or categories, or all? How would they be added to an order? What is the voucher amount, and is it a percentage of something or a money value?

[@younesalshammaa1575]

What program do you use to draw erd?

[@DatabaseStar]

I use LucidChart

[@carthai4590]

you're the best

[@DatabaseStar]

Thanks!

[@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]

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

[@iShubhamPanchal]

Hi sir, thanks for this fantastic video, I am new to your channel. Can you tell me how to do seller database design like registration of the seller, verification and then seller dashboard.

[@DatabaseStar]

Thanks, glad you like the video! What do you mean by “seller database”? Do you have an example of an existing system or website that does this?

[@iShubhamPanchal]

I am sorry for the confusion. I was confused too but I found the thing I was looking for......."Multivendor e-commerce website" database design. Can you guide me sir how can I design this type of database for production.

[@nigelnyakudya2262]

Thats what i need also

[@kanodekoubillybrown3025]

Why not include purchase history ?

[@DatabaseStar]

The purchase history is captured in the shop_order table.