Prisma Schema Design for an E-Commerce Database

A good Prisma schema for e-commerce has a few clear models (User, Vendor, Product, ProductVariant, Order, OrderItem), stores money as integer minor units, copies the price onto each order item at purchase time, and creates orders inside a $transaction that also decrements stock. Get those four habits right and most of the other decisions are easy to change later. I'm a final-year Computer Science student at KFUEIT, and I used Prisma for database management, query optimization and migrations in GoCart, my multi-vendor e-commerce final year project. The examples here use PostgreSQL syntax, but everything shown works with any relational provider Prisma supports.

What models does an e-commerce schema need?

Start with the nouns in your requirements. For a marketplace they are users, vendors, products, variants, orders and order items. A customer-only shop can drop Vendor and keep the rest. Two splits cause the most confusion. A product is the thing people browse ("Cotton T-shirt"), while a variant is the thing they buy (size M, blue) and holds the SKU, price and stock. And an order is the receipt header while order items are its lines. If you want the bigger picture of how vendors and sub-orders fit around this, read How to Build a Multi-Vendor E-Commerce Platform.

What does the full schema.prisma look like?

Here is a compact version. The sections after it explain each choice.

Prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum Role {
  CUSTOMER
  VENDOR
  ADMIN
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  COMPLETED
  CANCELLED
}

model User {
  id           String   @id @default(cuid())
  email        String   @unique
  passwordHash String
  role         Role     @default(CUSTOMER)
  vendor       Vendor?
  orders       Order[]
  createdAt    DateTime @default(now())
}

model Vendor {
  id       String    @id @default(cuid())
  name     String
  slug     String    @unique
  ownerId  String    @unique
  owner    User      @relation(fields: [ownerId], references: [id])
  products Product[]
}

model Product {
  id          String           @id @default(cuid())
  vendorId    String
  vendor      Vendor           @relation(fields: [vendorId], references: [id])
  title       String
  slug        String
  description String?
  variants    ProductVariant[]
  createdAt   DateTime         @default(now())

  @@unique([vendorId, slug])
  @@index([vendorId])
}

model ProductVariant {
  id         String      @id @default(cuid())
  productId  String
  product    Product     @relation(fields: [productId], references: [id], onDelete: Cascade)
  sku        String      @unique
  priceCents Int
  stock      Int         @default(0)
  orderItems OrderItem[]

  @@index([productId])
}

model Order {
  id         String      @id @default(cuid())
  userId     String
  user       User        @relation(fields: [userId], references: [id])
  status     OrderStatus @default(PENDING)
  totalCents Int
  items      OrderItem[]
  createdAt  DateTime    @default(now())

  @@index([userId, createdAt])
}

model OrderItem {
  id             String          @id @default(cuid())
  orderId        String
  order          Order           @relation(fields: [orderId], references: [id], onDelete: Cascade)
  variantId      String?
  variant        ProductVariant? @relation(fields: [variantId], references: [id], onDelete: SetNull)
  title          String
  sku            String
  unitPriceCents Int
  quantity       Int

  @@index([orderId])
  @@index([variantId])
}

The Role enum is the starting point for permissions, and Role-Based Access Control in Next.js with Prisma shows how to enforce it in routes.

Should prices be Decimal or integers?

Never use Float for money. Binary floating point cannot represent 0.10 exactly, and the errors show up when you add up a cart. You have two sound options. Prisma's Decimal type (with a native type such as @db.Decimal(10, 2) on providers that support it) stores exact values, but your code receives a Decimal object rather than a JavaScript number, so you must convert it before sending JSON to the browser. Integer minor units (cents, paisa) are the other option: store 1999 for 19.99 and format only when you display it. I prefer integers for a simple reason: arithmetic stays in plain numbers, so totals, discounts and tax are easy to test. Whichever you pick, keep the currency in mind. If you ever sell in more than one currency, add a currency column next to each amount.

Why copy the price onto OrderItem?

Look again at OrderItem. It has its own title, sku and unitPriceCents even though the variant already has them. This is a snapshot. Prices change, products get renamed and variants get deleted. A paid order from last month must still show what the customer actually paid. If the order item only pointed at the live variant, a price edit would silently rewrite your sales history and your refunds would not add up. The same reasoning explains onDelete: SetNull on the variant relation. If a vendor removes a variant, the old order lines survive with their snapshot and a null link.

How do relations, indexes and onDelete rules work?

Every relation in Prisma needs a foreign key field and a @relation pair, as in vendorId and vendor. Prisma's own page on relations is the reference when a many-to-many gets tricky. PostgreSQL does not create an index for a foreign key column on its own (MySQL does), and Prisma does not add one for you either, so add @@index on the columns you filter by. Here that means vendorId for vendor dashboards, productId for loading variants, and userId plus createdAt for an order history page that sorts newest first. Use @@unique for rules the database should enforce. A slug only needs to be unique per vendor, so @@unique([vendorId, slug]) lets two shops both sell a "classic-tee". A global @unique on sku makes stock lookups unambiguous. For onDelete, required relations default to restricting deletion, which is the safe choice: you cannot delete a vendor who still has products. Use Cascade only for true children such as variants of a product or lines of an order, and SetNull where history must outlive the target.

What should you leave out of the first version?

A schema grows as the product grows, and adding a table later is cheap with migrations. Removing a wrong one after real orders exist is not. So resist modelling things you cannot yet describe in a sentence. Addresses are a good example. A first version can copy the shipping address onto the order as plain fields, which also gives you a snapshot for free. A separate Address model with saved addresses per user can come when someone actually asks for it. The same goes for coupons, reviews and wishlists: each is a small table with a foreign key to User or Product, and none of them changes the models above. Soft deletes deserve a deliberate decision too. For products, an isActive boolean or an archivedAt timestamp is usually kinder than a real delete, because it hides the item from the catalogue without touching old orders. For orders themselves, change the status to CANCELLED instead of deleting the row.

How do you create an order and decrement stock safely?

Two customers can buy the last item at the same moment. The fix is to do everything in one transaction and make the stock update conditional, so the database decides who wins.

type CartLine = { variantId: string; quantity: number };

export async function createOrder(userId: string, lines: CartLine[]) {
  return prisma.$transaction(async (tx) => {
    let totalCents = 0;
    const items = [];

    for (const line of lines) {
      const variant = await tx.productVariant.findUniqueOrThrow({
        where: { id: line.variantId },
        include: { product: true },
      });

      const updated = await tx.productVariant.updateMany({
        where: { id: variant.id, stock: { gte: line.quantity } },
        data: { stock: { decrement: line.quantity } },
      });
      if (updated.count === 0) {
        throw new Error(`Out of stock: ${variant.sku}`);
      }

      totalCents += variant.priceCents * line.quantity;
      items.push({
        variantId: variant.id,
        title: variant.product.title,
        sku: variant.sku,
        unitPriceCents: variant.priceCents,
        quantity: line.quantity,
      });
    }

    return tx.order.create({
      data: { userId, totalCents, items: { create: items } },
      include: { items: true },
    });
  });
}

Notice three details. Prices are read from the database, never from the cart sent by the browser. The stock: { gte: quantity } condition means the update touches zero rows if someone else got there first. And throwing inside the callback rolls everything back, so a half-finished order never exists. Prisma documents the options in its transactions guide, including the default timeout for interactive transactions. Payment confirmation comes after this. A webhook flips the order from PENDING to PAID, as covered in Stripe Checkout in a MERN Stack App.

When should you use include and select?

include adds relations to the full model, and select picks exact fields. Use select for lists and API responses so you do not send password hashes or load columns you never display.

const products = await prisma.product.findMany({
  where: { vendorId },
  select: {
    id: true,
    title: true,
    variants: { select: { sku: true, priceCents: true, stock: true } },
  },
  orderBy: { createdAt: "desc" },
  take: 20,
});

You cannot use include and select at the same level of one query, so pick one per level. Always paginate with take (and a cursor or skip) on catalogue and order lists.

How do migrations work, migrate dev or migrate deploy?

Use migrate dev on your machine. It compares the schema with your migration history, writes a new SQL file, applies it and regenerates the client. Use migrate deploy on staging and production, where it only applies migrations that already exist in version control and never prompts or resets.

Bash

npx prisma migrate dev --name add-order-items
npx prisma migrate deploy

Commit the generated prisma/migrations folder. Review the SQL before you commit, especially for renames, which Prisma may generate as a drop and create. The official page on development and production workflows lists the edge cases. Cover photo from Unsplash.