Building eCommerce is one of the most famous web application that we build as it got so famous, well known, and the need of it is became more than ever for ordering and shipping products to your home online.
One of the most challenges that I have faces while building many eCommerce application is the complicity that it gain with more features that's required from the client and we have to add. That's made me think about the pros and cons of my structure specially the database design and started ask my self many questions like, Is my design is suitable for these requirement?, Did I get the most clean structure I can get?, Is this affect with performance and loading data efficiency?.
Until this moment I write this blog, this the database design that I get into conclusion to be best fit to the needs that I have faced before (until I got new project with more complicated requirement 🤡).
Our Project Description & Requirements
First of all, as any application we have users that can register and login to our eCommerce application. Every user have one or more addresses. They have also favorite or Wishlist, Orders list to list there orders and its status, and Cart list of all the products that's ready to be ordered.
We have categories and sub categories (2 levels of categories) and each have a name and description.
For sure we have products and this have many things:
- Basic information of the product like: name, description, price, summary, cover image, ...etc.
- Product gallery which consist of many images of the product.
- Every product may have some attributes like sizes, and colors.
- Some products that have many colors will have image gallery for every color separated.
- Every color, size, or both might there prices vary. let's say that the blue t-shirt will cost
20$
but the red t-shirt will cost22$
. even the xl blue t-shirt will cost22$
but the XXL blue t-shirt will cost26$
. - Every color and size have limited quantity stock like we might have from the XXL blue t-shirt 3 items but XXXL blue t-shirt just 1 item.
When user make an order will choose one of his addresses with the order and he might pay online with any supported way like visa or something else (will integrate with payment gateway).
When the order is out for delivery then the quantity of all products will decrease.
Now, that's all with the requirements of the project so let's get our mind around it.
Database Designing
As we notice from the previous section that the most complex part is the product, here where the game play go xD.
Let's start from the beginning of our database design.
Users Table
Let's start with the users table and its related information.
Here we just have 2 tables, one for users information details, and the other one for the addresses that's associated to the user and linked in one to many
relationship using the user_id
.
Categories Table
Now in the categories table, We have 2 tables one for main categories, and the other for sub categories. sub categories table associated to the categories in one to many
relationship.
Products Table
Now, The products table, Let’s start with simple structure that have the basic information of the product and linked within the categories (will be linked in many to many
relationship with sub categories table). See image bellow.
Let’s try to take a more higher look, as mention above the products will have many different attribute (in our situation here are colors, and sizes) and they might be with different prices range, So let’s remove the price column from the products table because that will be linked in other table.
As we notice above, we added new table called product_attributes
that will hold all product attributes types and there values from different colors and sizes.
Then will create product_skus
table that will link between products and the attribute together as we added 2 columns both point to the product_attributes
table but, one for colors and the other for sizes (we can add more if we need to).
Note: We may split the
product_attributes
table to 2 more table likeproduct_colors
, andproduct_sizes
but I think ofproduct_attributes
table idea more easily extendable as we can add more attributes sizes in same table but will add just one more column inproducts_skus
table.
Each product_skus
entity have a price and quantity value that may vary between every attributes variation.
Wishlist Table
The Wishlist table is quite simple as we will just store the product_id
and the user_id
. That’s it 😀.
Cart Table
This table is what’s holding the products that customer wants to buy and order so we will have a table called cart
which connected to the user
table and have total cost attribute.
The other table is cart_item
which is linked to the cart table and carry the product
and product sku
tables and the quantity required by the customer.
As shown above, the 2 tables are very simple.
Orders Table
The order table is same as cart tables we have order
and order_item
(but will call the order
as order_details
). Just will have one more table that will be responsible for the payments will be named payment_details
which will hold all the payment information from order, amount, provider, and status.
As shown above, this is the order and payment tables to store all needed info that we need for now or event we can add more attributes as required.
Final Schema
Now let’s look into the big picture of our schema.
Here’s the preview link to play with it on dbdiagram.io
Conclusion
Here we reach to the end of this article, I hope you enjoyed. If you have any suggestion or comments, please write it down and I'll be happy to share thoughts with you.
See you again 😀;
Top comments (10)
Nice post, Ezzdin! The database design is really good, and I really enjoyed reading this.
Here are my suggestions -
In the context of
cart_item
andorder_item
, you probably don't needproduct_id
as it is redundant. The required data can be fetched usingproduct_skus_id
.Storing the total directly in the cart table introduces redundancy, as the total is calculated based on the individual product prices or quantities. This redundancy can lead to data integrity issues. Unless you are using some event-driven mechanism such as database triggers, instead of storing the total directly in the database, you might need to calculate the total dynamically when needed.
Thank you so much for your positive feedback and insightful suggestions! I'm thrilled to hear that you found the article interesting.
You have a good point about the
product_id
redundancy, and I will updated the structure with this optimization.Regarding the storage of the total directly in the cart table, I appreciate your concern about redundancy and potential data integrity issues and instead of that we can calculate the total dynamically when needed, and actually I don’t know much about the event-driven mechanism and will lock into that. Thanks Adhiraj!
Why not just have one categories table, set parent to null.
Thank you for your suggestion! Having a single categories table with a parent set to null is indeed a valid approach in certain scenarios. This depends on the specific requirements of your application.
However, in some cases, a separate table for parent and child categories can provide additional benefits like allowing easy management of attributes of every category type and simplifies certain types of queries.
Interesting. Could you expound on this? Would like to see benefits...
Yes! In certain instances, I chosen to simplify the
categories
table by storing only theid
andname
of the category. Meanwhile, thesub_categories
table includes additional details such asid
,name
,parent_id
,description
,icon
, and aflag
to signal anything new or updated in the UI. Additionally, I introduced agroup_id
for cases where subcategories are logically grouped under a common title.In other scenarios, I have followed your recommended approach, especially when a client requested categories and subcategories with unlimited depth. Your approach proved to be the most suitable for handling such cases of dynamic hierarchy.
I’m glad that you find the dbdiagram.io tool helpful to you and appreciate your suggestion about the
payment_details
table, it’s nice suggestion I will keep in mind next time 😀Could you publish the DDL?
You can export it from dbdiagram.io tool, as I have shared a link to the database design from that tool where you can find an export button to export it. That Database Design