Database design for e-commerce systems

Most e-commerce bugs I’ve seen are not frontend bugs. They are data bugs: a price that changed after an order was placed, stock that went negative, a report that doesn’t match the bank statement. Nearly all of them trace back to a handful of schema decisions made in the first week.

These are the rules I follow when designing the database for a store, a marketplace or a digital-goods platform. The examples use MySQL and Laravel migrations, but the ideas apply to PostgreSQL and any other stack.

Store money as integers

Never store prices in FLOAT or DOUBLE. Floating-point numbers can’t represent most decimal values exactly, and the rounding errors show up in totals.

Store the smallest currency unit as an integer — paisa for NPR, cents for USD:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->unsignedBigInteger('price'); // Rs 1,250.50 is stored as 125050
    $table->char('currency', 3)->default('NPR');
    $table->timestamps();
});

DECIMAL(12,2) is also safe if you prefer it. The important thing is to pick one approach and use it everywhere — in the database, in PHP, and in any API you expose.

Orders are snapshots, not references

A product’s name and price will change. An order placed last month must still show what the customer actually bought and paid.

So order_items copies the relevant product data at the moment of purchase instead of relying only on a foreign key:

Schema::create('order_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained()->cascadeOnDelete();
    $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
    $table->string('product_name');          // snapshot
    $table->unsignedBigInteger('unit_price'); // snapshot
    $table->unsignedInteger('quantity');
    $table->unsignedBigInteger('line_total');
});

Keep the product_id for analytics, but treat the copied columns as the source of truth for that order. The same applies to shipping addresses, tax rates and discounts.

Model order status explicitly

An order is a small state machine: pending → paid → processing → completed, with side exits like cancelled and refunded. Write the allowed states down and enforce transitions in one place in your code.

It also helps to keep a history table:

Schema::create('order_status_histories', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained()->cascadeOnDelete();
    $table->string('from_status')->nullable();
    $table->string('to_status');
    $table->foreignId('changed_by')->nullable()->constrained('users');
    $table->string('note')->nullable();
    $table->timestamp('created_at');
});

When a customer asks “why was my order cancelled?”, this table answers in seconds. For digital goods — top-ups, vouchers, gift cards — it is essential, because you need to prove exactly when something was delivered.

Track stock as movements, not just a number

A single stock column on products is easy to start with and hard to trust later. When the number is wrong, you have no way to find out why.

Record every change instead:

Schema::create('stock_movements', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained();
    $table->integer('quantity');        // +10 purchase, -2 sale, -1 damaged
    $table->string('reason');           // purchase, sale, return, adjustment
    $table->nullableMorphs('reference'); // the order, purchase, etc.
    $table->timestamps();
});

You can still keep a cached stock column for fast reads, updated in the same transaction as each movement. If the cache ever drifts, you can rebuild it from the movements. This pattern is just as useful in a POS system as in an online store.

Wrap multi-table writes in transactions

Placing an order touches several tables: orders, order_items, stock_movements, maybe payments and coupons. Either all of those writes succeed or none of them should.

DB::transaction(function () use ($cart, $user) {
    $order = Order::create([...]);

    foreach ($cart->items as $item) {
        $product = Product::whereKey($item->product_id)->lockForUpdate()->first();

        if ($product->stock < $item->quantity) {
            throw new OutOfStockException($product);
        }

        $order->items()->create([...]);
        $product->decrement('stock', $item->quantity);
    }

    return $order;
});

lockForUpdate() prevents two customers from buying the last item at the same moment.

Make payment processing idempotent

Payment gateways retry callbacks. Users double-click. Networks time out after the payment has already succeeded. Your system must handle “the same payment notification arrived twice” without charging, crediting or delivering twice.

The simplest tool is a unique constraint:

$table->string('gateway_reference')->unique();

If the insert fails because the reference already exists, you’ve already processed that payment — log it and return success.

Index for the queries you actually run

Add indexes based on real access patterns, not guesses. Typical ones for a store:

• orders (user_id, created_at) — a customer’s order history
• orders (status, created_at) — admin dashboards filtering by status
• order_items (product_id) — “how many of this product sold?”
• products (category_id, is_active) — category listing pages

Use EXPLAIN on slow queries before adding indexes, and remember every index slows down writes a little.

Soft-delete catalogue data, not financial data

Soft deletes (deleted_at) are useful for products and categories that admins might remove by mistake. Orders, payments and stock movements should generally never be deleted at all — cancel or reverse them with a new record instead. Accountants will thank you.

Summary

• Integers (or DECIMAL) for money, never floats.
• Orders copy product data at purchase time.
• Explicit order states with a history table.
• Stock as a ledger of movements.
• Transactions and row locks around checkout.
• Unique gateway references for idempotent payments.
• Indexes driven by real queries.

None of this is exotic, but getting it right on day one is far cheaper than repairing a year of inconsistent data.