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 😀;