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.

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

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

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

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

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

[@musicplayer9353]

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

[@DatabaseStar]

Glad it helped!

[@factzfactory4956]

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

[@DatabaseStar]

Glad to hear it!

[@rohan8758]

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

[@DatabaseStar]

Glad it was helpful!

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

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

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

[@simondolapo1567]

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

[@DatabaseStar]

Glad it was helpful!

[@dead_holicx3127]

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

[@DatabaseStar]

Glad it was helpful!

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

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

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

[@jaybharti5709]

Too good to start your eCommerce website development

[@DatabaseStar]

Thanks!

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

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

[@lyricass7810]

very nice explanation sir

[@DatabaseStar]

Thanks!

[@abahjosephisrael4459]

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

[@DatabaseStar]

You're most welcome!

[@onyekachifrancis4572]

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

[@DatabaseStar]

Good to hear!

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

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

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

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

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

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

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

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

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

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

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

[@circumz]

Excellent work sir

[@DatabaseStar]

Thanks!

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

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

[@appstuff6565]

Love this! Do you plan to have a recurring payment system database like how notion or spotify or any subscription service has? Liked and Subbed!

[@DatabaseStar]

Thanks! I can create a database design for that in the future.

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

[@Shkkmj6868]

You are a great explainer. Thanks a lot.

[@DatabaseStar]

You're welcome

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

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

[@franciscomarin5191]

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

[@DatabaseStar]

Awesome, thank you!

[@chris_ea]

Thanks! What diagram tool are you using?

[@DatabaseStar]

I’m using Lucidchart

[@halahal-hussien4781]

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

[@DatabaseStar]

Glad it was helpful!

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

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

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

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

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

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

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

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

[@triskominfoss]

Thanks for all this material

[@DatabaseStar]

No problem!

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

[@pratyakshgupta4787]

Great explanation.

[@DatabaseStar]

Thanks!

[@trishulcurtis1810]

Excellent Video!

[@DatabaseStar]

Thanks!

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

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

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

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

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

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

[@zhh174]

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

[@DatabaseStar]

Good idea!

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

[@DankMeme-10K-im4nq2gz4g]

thank you for the copy of diagram, brother.

[@DatabaseStar]

No problem!

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

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

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

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

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

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

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

[@tonyx233]

Very helpful. Well Done!

[@DatabaseStar]

Thanks!

[@SHINJAGIRAArnold]

you deserve a like and a subscribe 👍👍

[@DatabaseStar]

Thanks!

[@nmqbrightray]

Thank you, it's very helpful

[@DatabaseStar]

Glad it was helpful!

[@deepeshchhetri850]

Thank you so much for the video! Very informative. I have a question. How can we effectively monitor a product in stock once an order has been placed? Should we calculate the remaining stock by subtracting the ordered quantity from the total for each transactions without changing the qty_in_stock, or should we update the qty_in_stock value in the product_item table after each transaction? In the latter scenario, what occurs during concurrent operations?

[@DatabaseStar]

Glad you liked it! Good question. There are a couple of ways it could be done I think. My initial thoughts are to update the remaining stock after each order (the second of your suggestions). For concurrent operations, you would probably need to contain this process within a database transaction so it completes successfully.

[@deepeshchhetri850]

​@@DatabaseStar I greatly appreciate your response, as it addresses the question I've had for some time. Moreover, this approach can be used in numerous transactions. Thank you again!

[@andrewnguemnang2757]

How can i query a product with variation

[@DatabaseStar]

You could select from the two tables using a join.

[@allisfehr]

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]

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

[@DatabaseStar]

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]

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.

[@jimmyhung6087]

where is the line of shop_order -> user_id line ?

[@DatabaseStar]

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

[@andresfelipelimaszea7151]

Really good video

[@DatabaseStar]

Thanks!

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

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

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

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

[@pinh4133]

thats all I need Thank you

[@DatabaseStar]

You're welcome!

[@pinh4133]

@@DatabaseStar so bro, how can I query all information relates to the product? By using Node JS like MongoDB

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

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

[@hwp438]

Loved it. Thankyou

[@DatabaseStar]

Glad it was helpful!

[@carthai4590]

you're the best

[@DatabaseStar]

Thanks!

[@AbhishekSingh-tf6sx]

Awesome!

[@DatabaseStar]

Thanks!

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

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

[@kanodekoubillybrown3025]

Why not include purchase history ?

[@DatabaseStar]

The purchase history is captured in the shop_order table.

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

[@anarkholico]

Hi, this is great. Thank you so much for your work. A question: How would you put a new Supplier entity in the middle of this? Considering that a product can have many different Suppliers and a Supplier can have also many different products? Would you create a new table in the middle of Supplier and Product, in the middle of Product_Item and Supplier or in a diferrent place? Thanks again.

[@DatabaseStar]

Good questions. Yes, you could have a table between supplier and product, as this sounds like a "many-to-many" relationship.

[@andersonoki2172]

@@DatabaseStar in my current E-commerce design i have the supplier table because i am building a platform that will give a businessman some freedom along with some automation

[@anarkholico]

@@DatabaseStar Thanks. I did that. Now my problem is that I have a supplier table that looks similar to the users table (e.g. it has name, phone number and multiple addresses too). What would you recommend I do? 1. Create another table for suppliers_addresses (and also maintain the Users and users_addresses table) 2. Or create a more abstract table like for example Persons that contain all the possible fields from suppliers and users and also a TYPE field to determine if it´s a user or a supplier? Thanks again.

[@toyly2820]

@@anarkholico Man, i tried to do something similar. I tried situation where one product can have many sellers, and of course seller can have many products. But that is disaster to manage. it is better if one seller has product same as other seller. Otherwise, how would you manage situation if seller's product has two colors and each has 2 sizes while other seller has just one color different from other seller's two color with different size? Seller_id should be going mentioned in all variation_options coz if it was just for product, then it has a lot of options but available on different sellers partly. So my suggestion is, worldwide famous e-commerce websites dont do that, better you not too. It is only ok if it is price compare website where you actually need not user to choose between variations of products, but just see the product and price for it.

[@animecontent232]

@@DatabaseStar I think the supplier can have many products while the product can have only on supplier.

[@OmarAhmed-zz7vw]

what is the website you used to draw erd

[@DatabaseStar]

I use a tool called LucidChart.