DEV Community

Cover image for eCommerce Database Design
Ezzdin Atef
Ezzdin Atef

Posted on

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.

Users Table

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.

Categories Table

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.

Products Table

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.

Products 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 😀.

Wishlist Table

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.

Cart Table

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.

Orders Table

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.

Final 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 (11)

Collapse
 
jdgamble555 profile image
Jonathan Gamble

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

Collapse
 
ezzdinatef profile image
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.

Collapse
 
jdgamble555 profile image
Jonathan Gamble

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

Thread Thread
 
ezzdinatef profile image
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.

Collapse
 
adhirajk profile image
Adhiraj Kinlekar • Edited

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.

Collapse
 
ezzdinatef profile image
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!

Collapse
 
kwnaidoo profile image
Kevin Naidoo

This is great! I like the dbdiagram.io tool you using to declare SQL and generate a diagram - pretty cool.

I have just one suggestion, your payment_details table. I would have a second table or a few extra columns just to catch more meta information from the payment gateway provider.

Depending on the provider, if a payment fails - they tend to give you some additional log information which is important to store for later recon. Even with success payments, the recon information can be useful for generating reports.

Collapse
 
ezzdinatef profile image
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 😀

Collapse
 
polterguy profile image
Thomas Hansen

Could you publish the DDL?

Collapse
 
ezzdinatef profile image
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