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!
There are no SQL scripts to create this database, but if you want to create it, you can learn how to create an SQL script from a database design in this video:
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
/// RESOURCES
Bonus PDF: This ERD, descriptions of each table and column with sample data, and SQL scripts to create all of the tables on your own database:
SQL Roadmap: an overview of SQL topics, which is a great place to start:
Contact me here for questions, sponsorship requests, and more:
Want to take your database and SQL skills to the next level? Sign up for Database Star Academy, the online platform for database development and SQL. Check it out here:
Links:
ERD diagram shown in this video (a PNG file):
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: https://www.databasestar.com/dbdesign/
Can this be used for NoSql or Non relational database? Like MongoDB ??
thanks for great vedio, can we put the variation option table as an attribute to the variation table?
~@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.
Really good video
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).
please can you add vouchers in this scenario for me ? or create a separate detailed video for vouchers ?
What do you do for department and category images?
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.
usually, do backend developer do all this job? There should be a DBA position to handle this
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
Amazing explanation, Thanks a lot
Thank you so much!
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!
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?
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?
How can i query a product with variation
Please what is product configuration
What program do you use to draw erd?
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