How Do You Design a Shopping Cart Database for E-Commerce?

An e-commerce shopping cart serves as a virtual cart that allows customers to add and hold items until they complete the purchase. It accepts payments of customers, organizes and distributes all order information to the merchant, customer, and other relevant parties.

This process requires a database to store and retrieve the relevant data while supporting the functionality of the shopping cart. A shopping cart database will contain all the critical information about products, orders, and customers and allow users to perform real-time changes reflected in their shopping session.

Designing the Database

A shopping cart database should be highly available, fault-tolerant, and highly responsive to provide customers a smooth shopping experience 24x7. When designing a shopping cart database, it can be divided into three main components for better categorization and understanding of the underlying data structure.

1. Static Data
2. Session Data
3. Processed Data

Static Data

This component will include somewhat static data that the customer needs only to retrieve while interacting with a shopping cart. The data is stored in the following types of tables:

• product table
• discount table
• user table

Session Data

This is the most important component of the shopping cart database where all the live interactions (session details) are stored when the client is interacting with the shopping cart.

• shopping_session table
• cart_item table

Processed Data

Once the customer completes a transaction, we need to permanently store the order information by moving the Session Data into permanent storage. Additionally, we need to store the payment details.

• order_details table
• order_items table
• payment_details table

Table Relationships in Database

The following diagram demonstrates the relationships within the above-mentioned tables inside the database using a sample fieldset. The fields in the tables may depend on the requirements of the specific e-commerce platform and can range from a simple to complex list of fields.

When designing the database, we need to have a good balance between simplicity and covering the required functionality.

Let's dig a bit deeper into the structure of the shopping cart database.

Static Data Component

In a shopping cart, tables like product and discount are only required to reference the product, inventory, and pricing details. They will only get SELECT queries when a customer adds an item to the shopping cart. The only time the product table gets updated is when a purchase is completed and needs to update the inventory of the products (UPDATE statement). Regular updates for these tables are made by the administrators of the e-commerce platform and should be a part of the product information management system.

The user table is only needed in the shopping cart to link the orders and sessions with the registered users. This allows the e-commerce platform to map the orders with the relevant users. The user details table is updated only when a new user is created, or when a user updates their details. This functionality is out of the scope of the shopping cart. Within the shopping cart, we only map the order/session with the user.

Since we only retrieve the data and limit write queries to updating inventory, the product table is considered a static data component in the shopping cart.

Example Table Structure (Product Table)

    CREATE TABLE `shopping_cart`.`product` (
        `id` INT(10) NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(100) NOT NULL,
        `desc` TEXT NOT NULL,
        `SKU` VARCHAR(50) NOT NULL,
        `category` VARCHAR(50) NOT NULL,
        `price` DECIMAL(6) NOT NULL,
        `discount_id` INT(5) DEFAULT '0',
        `created_at` TIMESTAMP NOT NULL,
        `modified_at` TIMESTAMP,
        UNIQUE KEY `prod_index` (`id`) USING BTREE,
        UNIQUE KEY `sku_index` (`id`,`SKU`) USING BTREE,
        PRIMARY KEY (`id`),
        CONSTRAINT `fk_prod_discount`
            FOREIGN KEY (`discount_id`)
            REFERENCES `shopping_cart`.`discount` (`id`)
            ON DELETE SET NULL
            ON UPDATE SET NULL
    ) ENGINE=InnoDB;

Session Data

This component contains the highly active tables within the database, facilitating the real-time functionalities of the shopping cart. The purpose of the shopping_session and cart_item tables is to act as highly efficient and temporary storage to support the live interactions of a customer with the shopping cart. When a customer visits the e-commerce platform, a session is created (shopping_session), and each item added to the cart is captured in the cart_item table linked to the specific session.

This enables us to capture the state of the shopping cart regardless of the customer interactions in the e-commerce platform. Combining these details with website cookies enables us to provide previous shopping cart details even if the customer navigates out of the e-commerce platform.

A considerable architectural effort is required to streamline these tables to support all kinds of live queries (SELECT, UPDATE, DELETE) made to the database without hindering the user experience. This data set also allows the retailers to understand any inefficiencies in their shopping experience by identifying the behavioral patterns of the customers. For example, if some customers are dropping out at the payment stage, the platform developers can drill down and identify any issues with the payment processing.

Example Table Structure (shopping_session table)

    CREATE TABLE `shopping_cart`.`shopping_session` (
        `id` INT(30) NOT NULL AUTO_INCREMENT,
        `user_id` INT(10) DEFAULT NULL,
        `total` DECIMAL(10) NOT NULL DEFAULT '0.00',
        `created_at` TIMESTAMP NOT NULL,
        `modified_at` TIMESTAMP,
        UNIQUE KEY `session_index` (`id`,`user_id`) USING BTREE,
        PRIMARY KEY (`id`),
        CONSTRAINT `fk_shopping_user`
            FOREIGN KEY (`user_id`)
            REFERENCES `shopping_cart`.`user` (`id`)
            ON DELETE SET NULL
            ON UPDATE SET NULL
    ) ENGINE=InnoDB;

Processed Data

The processed data contains the completed order details with the associated payment details. When a transaction is completed, we move the relevant data set from the shopping_session to order_details table and cart_item to order_item table and delete those records from the shopping_session and cart_item tables as they are no longer needed.

We can use a single table group (shopping_session/cart_item or order_details/cart_item) with an extra field (e.g. order_status) to indicate whether the order has been completed. However, this will create a bloated data set and negatively impact the performance of the e-commerce platform.

By separating the data into two distinct groups, we can keep track of the completed order in a separate table. This method allows maintaining the history of the orders while reducing the load of the shopping_session and cart_item tables to only contain data that needs to facilitate live interactions.

The combination of order_details and the order_item tables with the payment_details table creates the complete order details and enables the e-commerce platform to arrange the post-processing and distribution of the products or services.

Another advantage of the processed data component is that it can be used for analytics purposes. Matching the data with relevant users of the e-commerce platform enables us to provide suggestions based on previous purchases and carry out targeted marketing campaigns.

Example Table Structure (order_details table)

    CREATE TABLE `order_details` (
        `id` INT(20) NOT NULL AUTO_INCREMENT,
        `user_id` INT(10),
        `total` DECIMAL(10) NOT NULL,
        `payment_id` INT(20) NOT NULL,
        `created_at` TIMESTAMP NOT NULL,
        `modified_at` TIMESTAMP,
        UNIQUE KEY `order_index` (`id`) USING BTREE,
        UNIQUE KEY `customer_order_index` (`id`,`user_id`) USING BTREE,
        PRIMARY KEY (`id`),
        CONSTRAINT `fk_shopping_user_order`
            FOREIGN KEY (`user_id`)
            REFERENCES `shopping_cart`.`user` (`id`)
            ON DELETE SET NULL
            ON UPDATE SET NULL,
        CONSTRAINT `fk_order_payment`
            FOREIGN KEY (`payment_id`)
            REFERENCES `shopping_cart`.`payment_details` (`id`)
            ON DELETE SET NULL
            ON UPDATE SET NULL
    ) ENGINE=InnoDB;

Expanding the Scope of the Database

Shopping cart databases are only a single part of a vast e-commerce experience. This section will briefly explain how to extend the database to cover additional functionalities by introducing new tables and fields to the existing database.

User Details

A User table can be extended with other tables such as user_address and user_payment to store user preferences. Thus, it enables a smooth shopping experience by providing the stored details of the user for a faster checkout process.

Product Details

Combining additional tables like inventory and category to the products table enables us to expand the functionality of product management. This is a key consideration when expanding the e-commerce platform to integrate Product Information Management (PIM) functionalities.

A shopping cart database can be extended further to support any requirement. The database can act as a standalone database powering the complete e-commerce platform or a part of a database cluster focused on shopping cart functionality. The possibilities are limited only by the development effort and the user requirements.

In all instances, it is advisable to decouple the tables and create separate tables other than creating a few large tables. This increases the flexibility and the overall performance of the database while reducing data redundancy.

As an additional note, it's a good idea to plan a reliable backup and disaster recovery strategy to the database from the initial deployment of the database. It will increase the resilience of the database and offer peace of mind to the platform administrators.

OMS Software and Shopping Cart APIs

Order Management Software (OMS) provides everything needed to receive, track, and fulfill customer orders online. These solutions expose the shopping cart database through an API and allow e-commerce platform administrators to ensure that all order and inventory data is up to date.

For instance, with Fabric OMS, you can streamline the way of managing orders using a single dashboard and track inventory and orders across multiple channels.

OMS software also comes with shopping cart APIs that add flexibility to the shopping experience by enabling customers to edit their cart, apply promotional codes, and specify shipping and billing information.

For example, a user can use PATCH /cart/{cartId}/items to modify their cart, and the API endpoint is designed to ensure the cart is up to date at any given point. Below, you can see two sample requests for a registered and guest user.

Registered User (Logged in User)

    {
        "cartId": 604638499041,
        "userAuthToken": "6^t@CDm6DY7FZZq3E!0lT3rxb02d7&",
        "registeredUser": true,
        "items": [
            {
                "itemId": "1000000122",
                "quantity": 2,
                "group": [
                    "5e31a1f9fcc2b500089c10e8"
                ],
                "price": {
                    "sale": 0,
                    "base": 120,
                    "discount": {
                        "price": 0
                    },
                    "currency": "USD"
                    },
                "extra": {}
            }
        ]
    }

Guest User

    {
        "cartId": 604638499041,
        "userAuthToken": null,
        "registeredUser": false,
        "items": [
            {
                "itemId": "1000000015",
                "quantity": 5,
                "group": [
                    "LPCUsIdKqZhjHoA1Ok3tMCsc"
                ],
                "price": {
                    "sale": 10,
                    "base": 50,
                    "discount": {
                        "price": 0
                    },
                    "currency": "USD"
                    },
                "extra": {}
            },
            {
                "itemId": "1002200074",
                "quantity": 1,
                "group": [
                    "3NXSiwNoKbQxe5pbM9hc10lb"
                ],
                "price": {
                    "sale": 0,
                    "base": 450,
                    "discount": {
                        "price": 0
                    },
                    "currency": "USD"
                    },
                "extra": {}
            }
        ]
    }  

The Order Management System API provides different endpoints that reflect different functionalities. Below are some of the functionalities available through the Fabric Cart API.

• Merge guest cart with user cart
• Get cart by cartId or userId
• Add Ship-To to line items
• Get cart by Ship-To Id
• Apply/Remove Promo
• Create Bill-To records (billing details)

Building vs Buying the Shopping Cart

OMS technology available from e-commerce SaaS vendors like Fabric features robust APIs that provide almost limitless functionality. Designing a shopping cart database using them from the ground up may not be the best use of resources. However, if you want to build a shopping cart database from scratch for one reason or another, this article should point you in the right direction.

For further assistance, you can check out this guide on creating a database from start to finish using Microsoft Access and have an in-depth look at data modeling for e-commerce applications.