Database Design for E-commerce Websites: Transactions, Catalogs, and Orders

Database Design for E-commerce Websites: Transactions, Catalogs, and Orders

By VPS.DO
February 5, 2026

Designing a database for an e-commerce platform in 2026 requires balancing data integrity, query performance, scalability, and flexibility—especially for variable product attributes, real-time inventory, and ACID-compliant transactions during high-concurrency checkout flows.

Most production systems use PostgreSQL (or distributed SQL like CockroachDB/Yugabyte) as the primary transactional store, combined with Redis for carts/sessions, Elasticsearch/OpenSearch for catalog search, and sometimes JSONB columns or hybrid models for product attributes. The design follows relational principles with selective denormalization for speed, strong foreign keys for integrity, and careful indexing.

Core Goals and Tradeoffs

ACID transactions — Critical for checkout (order creation + inventory decrement + payment reservation) to prevent overselling.
High read throughput — Catalog/browse/search must be fast; use read replicas, caching, or denormalized views.
Flexibility for variants/attributes — Support clothing sizes/colors, electronics specs without schema changes.
Auditability — Orders and transactions need history (snapshots, event sourcing optional).
Scalability — Sharding by tenant/region or horizontal partitioning for orders.

Main Entities and Relationships

A practical schema centers on these core tables:

users / customers — User accounts and profiles.
products — Core catalog items (parent for variants).
product_variants — Specific SKUs (size/color combos).
inventory — Stock levels per variant/warehouse.
carts / cart_items — Transient shopping carts (often in Redis).
orders — Completed purchases.
order_items — Line items in an order (price snapshot).
payments — Transaction records.
categories, brands, attributes — Supporting catalog structure.

Common relationships:

One User → Many Orders
One Order → Many Order_Items
One Product → Many Variants
One Variant → Many Inventory records (multi-warehouse)
One Order_Item references Product + Variant (for history)

Recommended Schema (PostgreSQL Style)

SQL

-- Users / Customers
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255),
    first_name      VARCHAR(100),
    last_name       VARCHAR(100),
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE addresses (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT REFERENCES users(id) ON DELETE CASCADE,
    type        VARCHAR(20) CHECK (type IN ('billing', 'shipping')),  -- or separate tables
    street      VARCHAR(255),
    city        VARCHAR(100),
    state       VARCHAR(100),
    postal_code VARCHAR(20),
    country     VARCHAR(100),
    is_default  BOOLEAN DEFAULT FALSE
);

-- Catalog: Products & Variants
CREATE TABLE categories (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    parent_id   BIGINT REFERENCES categories(id),  -- hierarchical
    slug        VARCHAR(150) UNIQUE
);

CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    sku             VARCHAR(50) UNIQUE,
    name            VARCHAR(255) NOT NULL,
    description     TEXT,
    category_id     BIGINT REFERENCES categories(id),
    brand           VARCHAR(100),
    base_price      DECIMAL(12,2),
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    is_active       BOOLEAN DEFAULT TRUE
);

-- Flexible attributes: hybrid approach (fixed columns + JSONB)
CREATE TABLE product_variants (
    id              BIGSERIAL PRIMARY KEY,
    product_id      BIGINT REFERENCES products(id) ON DELETE CASCADE,
    sku             VARCHAR(50) UNIQUE NOT NULL,
    attributes      JSONB,               -- {"color": "Blue", "size": "M"}
    price           DECIMAL(12,2),
    cost            DECIMAL(12,2),
    weight          DECIMAL(8,2),
    images          JSONB[]              -- array of image URLs
);

-- Inventory (per variant, per location)
CREATE TABLE inventory (
    variant_id      BIGINT REFERENCES product_variants(id),
    warehouse_id    BIGINT,              -- or location reference
    available_qty   INTEGER NOT NULL DEFAULT 0,
    reserved_qty    INTEGER NOT NULL DEFAULT 0,
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (variant_id, warehouse_id)
);

-- Orders & Transactions
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT REFERENCES users(id),
    status          VARCHAR(30) NOT NULL DEFAULT 'pending',  -- pending, paid, shipped, delivered, cancelled
    total_amount    DECIMAL(12,2) NOT NULL,
    subtotal        DECIMAL(12,2),
    tax_amount      DECIMAL(12,2),
    shipping_amount DECIMAL(12,2),
    discount_amount DECIMAL(12,2),
    shipping_address JSONB,   -- snapshot at order time
    billing_address  JSONB,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT REFERENCES orders(id) ON DELETE CASCADE,
    variant_id      BIGINT REFERENCES product_variants(id),
    quantity        INTEGER NOT NULL,
    unit_price      DECIMAL(12,2) NOT NULL,   -- snapshot price
    subtotal        DECIMAL(12,2) NOT NULL,
    tax             DECIMAL(12,2) DEFAULT 0
);

CREATE TABLE payments (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT REFERENCES orders(id),
    amount          DECIMAL(12,2) NOT NULL,
    currency        CHAR(3) DEFAULT 'USD',
    status          VARCHAR(30) DEFAULT 'pending',  -- pending, authorized, captured, refunded
    gateway         VARCHAR(50),           -- stripe, paypal, etc.
    transaction_id  VARCHAR(100),
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

Key Design Decisions and Best Practices

Product Catalog & Variants
- Separate products (generic) from product_variants (SKUs).
- Use JSONB for dynamic attributes (color, size, RAM) → fast indexing with GIN.
- Avoid full EAV unless you need admin-defined attributes at runtime.
Inventory Management
- available_qty – what customer can buy.
- reserved_qty – locked during checkout (optimistic locking or Redis).
- Use triggers or application logic to enforce available_qty >= reserved_qty.
Orders & Transactions
- Snapshot prices/addresses in order_items and orders (JSONB) to handle price changes.
- Use ACID transactions for checkout:
  SQL
  BEGIN; -- Reserve inventory (UPDATE ... RETURNING) -- Create order -- Create payment intent COMMIT;
- Status machine prevents invalid transitions.
Performance Optimizations
- Indexes: composite on (category_id, price), (user_id, created_at) for orders.
- Partial indexes for active products.
- Materialized views or triggers for popular aggregates (top sellers).
- Cache hot catalog data in Redis/edge.
Scaling & Modern Patterns
- Read replicas for catalog/browse.
- Sharding orders by user_id or date range.
- Event sourcing for orders/payments if audit is critical.
- Polyglot: PostgreSQL (transactions) + Elasticsearch (search) + Redis (carts).

Quick Comparison: Schema Approaches for Attributes

Approach	Best For	Query Speed	Flexibility	Complexity
Fixed Columns	Predictable attributes	Fastest	Low	Low
JSONB (PostgreSQL)	Most modern e-commerce	Fast (with GIN)	High	Medium
EAV Model	Highly dynamic admin attributes	Slow	Very High	High
Hybrid	Core fixed + JSONB tail	Good	High	Medium

This design powers mid-to-large e-commerce platforms handling thousands of orders per minute while keeping inventory accurate and queries responsive. Start with PostgreSQL + JSONB for variants, enforce strong consistency on checkout paths, and layer caching/search engines for scale.

Database Design for E-commerce Websites: Transactions, Catalogs, and Orders