eCommerce Database Design: Diagram & Explanation

An eCommerce database design is a good example of a real application that you can design a database for. It has some simple and common concepts such as users, and some complicated features such as products, attributes, and orders.

In this video, you will:
- See my database design for a fictional eCommerce database
- Learn what requirements will be included in the design
- See an explanation of each table, why it was designed this way, and what each column is used for
- Understand how tables are related, and why, to support the features of an eCommerce database

This is just one example of an eCommerce database design, and it’s how I would design one. You may have other ways of designing it - feel free to share in the comments below!

Timestamps:
00:00 Our requirements
01:23 Full design
01:46 Site user and address
03:53 Payment method
05:01 Products
11:02 Shopping cart
11:46 Order
13:55 User reviews
14:33 Promotions

Links:

[cthwyzy]

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

[MiKu-kxlw]

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.

[davosonic]

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.

[simondolapo]

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

[rohan]

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

[jimmyjolling]

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!

[alexasiricortanayuen]

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.

[abahjosephisrael]

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

[dead_holicx]

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

[toyly]

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.

[franciscomarin]

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

[davem]

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?

[factzfactory]

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

[halahal-hussien]

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

[onyekachifrancis]

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

[luizetulio]

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)

[RANKKY]

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 😅

[hoangphan]

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

[jam]

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?

[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