eCommerce Database Design

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 cost 22$ . even the xl blue t-shirt will cost 22$ but the XXL blue t-shirt will cost 26$.
• 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 like product_colors, and product_sizes but I think of product_attributes table idea more easily extendable as we can add more attributes sizes in same table but will add just one more column in products_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 😀;

Comments

[Jonathan Gamble]

Why not just have one categories table, set parent to null.

[Ezzdin Atef]

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.

[Jonathan Gamble]

Interesting. Could you expound on this? Would like to see benefits...

[Ezzdin Atef]

Yes! In certain instances, I chosen to simplify the categories table by storing only the id and name of the category. Meanwhile, the sub_categories table includes additional details such as id, name, parent_id, description, icon, and a flag to signal anything new or updated in the UI. Additionally, I introduced a group_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.

[Adhiraj Kinlekar]

Nice post, Ezzdin! The database design is really good, and I really enjoyed reading this.

Here are my suggestions -

1. In the context of cart_item and order_item, you probably don't need product_id as it is redundant. The required data can be fetched using product_skus_id.

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

[Ezzdin Atef]

Thank you so much for your positive feedback and insightful suggestions! I'm thrilled to hear that you found the article interesting.

1. You have a good point about the product_id redundancy, and I will updated the structure with this optimization.

2. 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!

[Ezzdin Atef]

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 😀

[Khalaf Nasirov]

FOREIGN KEY (size_attribute_id) REFERENCES product_attributes(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (color_attribute_id) REFERENCES product_attributes(id)
ON DELETE CASCADE ON UPDATE CASCADE

In this case how each column size_attribute_id and color_attribute-id in products_skus knows which enum type is referring to?

EDIT
With this method i see all enum values in list. Imagine many attributes a product can have and each attribute could have sub attributes. Is there a way an id could direct into enum value specifically

[Janice]

Thank you for this article. I'm still learning backend stuff. I wonder if the enum product_attribute_type is extensible by a db query when user wants to add another attribute type for example?

[Thomas Hansen]

Could you publish the DDL?

[Ezzdin Atef]

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

[Kingsley Ugwudinso]

What if you have various attributes other than size and color?