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

Bradley Taylor

Bradley Taylor

November 12
  •  
4 minute read
Shopping cart database design

An e-commerce shopping cart serves as a virtual cart that allows customers to add and hold items before completing a purchase. It accepts the customer's payment and organizes and distributes all order information to the merchant, customer, and other parties.

Performing these functions requires shopping cart databases. These databases are designed to store vital information about products, orders, and customers while allowing users to make changes in real-time that are reflected in their shopping experience.

 

Designing shopping cart database tables

For customers to be able to add products, place orders, and save their personal or cart information, your shopping cart database must have the following tables:

  • Product table: stores all important product data
  • Order table: provides all the necessary information to manage your store orders
  • User table: stores user information and can be used to manage different user types including customers and admins
  • Cart item table: allows virtual carts to store items before creating an order; it also enables logged-in users to be associated with a cart

Below you can see the different columns you should include in your product table, according to this tutorial:

Id The unique id to identify the product.
User Id The user id to identify the admin or vendor.
Title The product title to be displayed on the Shop Page and Product Page.
Meta Title The meta title to be used for browser title and SEO.
Slug The slug to form the URL.
Summary The summary to mention the key highlights.
Type The type to distinguish between the different product types.
SKU The Stock Keeping Unit to track the product inventory.
Price The price of the product.
Discount The discount on the product.
Quantity The available quantity of the product.
Shop It can be used to identify whether the product is publicly available for shopping.
Created At It stores the date and time at which the product is created.
Updated At It stores the date and time at which the product is updated.
Published At It stores the date and time at which the product is published on the Shop.
Starts At It stores the date and time at which the product sale starts.
Ends At It stores the date and time at which the product sale ends.
Content The column used to store the additional details of the product.

 

To keep the design simple, this table uses one column to track available stock. You may need to break quantity out into several columns so you can display a different total to buyers.

 

More shopping cart database tables

There are additional database tables you can include to add more functionality to your shopping cart and further improve the shopping experience:

  • Product review: designed to store product reviews
  • Category: used to store the product categories and their mappings
  • Transaction: designed to track payments made by the buyer as well as any refunds

You can also use a product meta table to store additional information about products. This table will include the following columns:

  • Id
  • Product id
  • Key
  • Content

Below you can see how a product meta table with the appropriate constraints appears:

CREATE TABLE `shop`.`product_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `productId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_product` (`productId` ASC),
  UNIQUE INDEX `uq_product_meta` (`productId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_product`
    FOREIGN KEY (`productId`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

 

OMS software and shopping cart APIs

Order management software provides everything needed to receive, track, and fulfill customer orders online. These solutions come with a shopping cart database that syncs with your e-commerce platform to ensure that all order and inventory data is up to date.

For instance, with Fabric’s Order Management System (OMS), you can streamline how you manage orders with a single dashboard to track inventory and orders across multiple channels. 

OMS software also comes with shopping cart APIs that can be used to 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 a sample request.

{
    "items": [{
		"lineItemId": 1,
        "itemId": "1000000012",
        "quantity": 15,
        "price": {
        	"currency":"USD",
        	"sale": 0,
        	"base": 260,
        	"discount": {
				"price": 0
			}
        }
    },
    {
		"lineItemId": 2,
        "itemId": "1000000014",
        "quantity": 15,
        "price": {
        	"currency":"USD",
        	"sale": 0,
        	"base": 380,
        	"discount": {
				"price": 0
			}
        }
    }]
}

Other functional capabilities provided by Cart APIs include:

  • Merge guest cart with user cart
  • Get cart by cartId or userId
  • Add Ship-To to line items
  • Get cart by Ship-To Id

 

Building vs buying the shopping cart

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

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


Bradley Taylor

Bradley Taylor

November 12

Have questions?