Build an Ecommerce Database Schema
A foundational step in building an online store is how you organize its data in its database. There are many parts you need to keep track of as a user navigates your ecommerce store. The layout of your database is called a database schema. In an ecommerce store, you need to build a database schema that does one basic thing, get a user to move products to an order.
In this example, we will build an ecommerce store database schema from just three basic parts. After that, we can explore how we can branch out from there. The foundation of this guide was influenced from these three basic database schemas that you can check out yourself:
- https://creately.com/diagram/example/iosv0d302/E-commerce%20database%20schema
- https://github.com/ramortegui/e-commerce-db
- https://www.researchgate.net/figure/A-simplified-database-schema-for-e-commerce-transaction-processing_fig2_2359510
The three basic parts of our database will be a: user, product, and order.
Building the User
The user is the first foundational piece. The user interacts with the your online store. In your online store you have products. As the user clicks through the products, they eventually decide to purchase a few of those products. When a user selects a product they add that to their order.
The basic foundational pieces of a user is a: name, password and email address. You create a user to save their information so they can keep track of their purchases and payment options.
In our database, we don’t directly save a product to a user. We do that through an order. The order is what keeps track of the products. Therefore, a user has many products through and order.
Building the Product
The product is the second foundational piece. Your online store has many products. Each product needs a few important pieces of information such as: name, description, and price. As you add more products to your store, you can simply keep track of each with those three characteristics.
As a user selects products to purchase, we can save that information through an order and not directly to the user. The reason being is that a product can belong to many users, not just one user. If there would be no order, there would be no way to allow other users to buy the product. Therefore, a product has many users through an order.
If you would like to expand this piece, you can add categories and tag piece. This would allow your products to be easily sorted through in your store for users.
Building the Order
The order is the last foundational piece. As you can tell from the previous two examples, an order is the main transactional piece that allows users to interact with products through your store. The main foundational pieces of an order are: User ID foreign key, Product ID foreign key and the total amount of all the products.
As a user checks out of your store, this is what will allow your database to process the user and the products that that user will be purchasing. By storing all the necessary information in an order, you allow your database to save an instance of an interaction of a user with your stores products.
If you would like to expand this piece, you can add order items that would allow your user to have multiple copies of your product. Even deeper, you can allow your order item to have product details that would allow your product to be customized by size and/or color.
Conclusion
These three pieces are just the very basics of an ecommerce store. There are many other ways to build out an ecommerce store and to add features. As you build out yours, try to see how many ways you can expand on this basic schema. Below is a rough sketch of an ecommerce database schema I created and I hope it inspires you to create your own!
