Modelling the catalogue

A grocery catalogue looks simple and is not. Loose atta sold by the kilo, a branded pack sold by the piece, the same dal in three sizes, prices that change weekly, and stock that must be right at checkout. This lesson models it — and the decisions here shape every module that follows.

What a kirana shop actually sells

Before any schema, the awkward facts:

Products are sold in different units. Atta by weight, eggs by piece, milk by volume. A cart holding "2 atta" is meaningless without the unit.

The same product comes in sizes. Tata Salt 1kg and Tata Salt 2kg are one product to a customer browsing and two different things to stock and price.

Prices change, and an order must keep the price that was charged — not the current one.

Stock is per size, not per product.

Some things are weighed at packing. 1kg of tomatoes might be 1.05kg. Real shops handle this; we will note it and leave it out of scope.

The model

model Category {
  id       String    @id @default(cuid())
  slug     String    @unique
  name     String
  sortOrder Int      @default(0)
  products Product[]

  @@map("categories")
}

model Product {
  id          String    @id @default(cuid())
  slug        String    @unique
  name        String
  brand       String?
  description String?
  imageUrl    String?
  isActive    Boolean   @default(true)

  categoryId  String
  category    Category  @relation(fields: [categoryId], references: [id])

  variants    Variant[]

  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([categoryId, isActive])
  @@index([isActive, name])
  @@map("products")
}

model Variant {
  id          String   @id @default(cuid())
  sku         String   @unique
  label       String
  unit        Unit
  quantity    Decimal  @db.Decimal(10, 3)
  pricePaise  Int
  mrpPaise    Int?
  stock       Int      @default(0)
  isActive    Boolean  @default(true)

  productId   String
  product     Product  @relation(fields: [productId], references: [id], onDelete: Cascade)

  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([productId, isActive])
  @@map("variants")
}

enum Unit {
  GRAM
  KILOGRAM
  MILLILITRE
  LITRE
  PIECE
  PACKET
}

The decisions, and why

Variants are separate rows. "Tata Salt" is a Product; "Tata Salt 1kg" and "Tata Salt 2kg" are Variants with their own SKU, price and stock. This is the central decision and everything else follows from it.

The alternative — one row per size, with the name repeated — means a product's description, image and category are duplicated, and changing the description means updating three rows. Worse, there is no way to show "available in 1kg and 2kg" on one page.

Price and stock live on the variant, never the product. A price on Product cannot express two sizes, and it is the mistake that forces a painful migration later.

unit is an enum. A free-text unit column fills up with "kg", "Kg", "kilogram" and "KG" within a month. The database refuses anything not in the list.

quantity Decimal(10,3) — three decimal places, so 0.5kg and 250g are both expressible. This is a measurement, not money, so Decimal is right here where Int was right for price.

mrpPaise is separate and optional. The printed maximum price, used to show a discount. Storing "20% off" instead would mean recomputing the original price and getting rounding wrong.

onDelete: Cascade on the variant's product. Deleting a product removes its variants. Without a rule, the database refuses the delete and you get a foreign key error you have to handle.

isActive rather than deleting. A discontinued product still appears in old orders. Deleting rows referenced by history is how you end up with an order showing "unknown item".

Soft delete, said properly

isActive Boolean @default(true)

Every query must then filter:

this.prisma.product.findMany({ where: { isActive: true } });

The risk is forgetting, and an inactive product appearing in the shop. Two ways to reduce it: put every read behind a service method that applies the filter, so no controller queries Prisma directly; or use a Prisma client extension to apply it automatically.

Be aware of the cost: a @unique on slug still applies to inactive rows, so you cannot reuse a slug after deactivating a product. That is usually correct — reusing a URL for a different product is bad for anyone who bookmarked it.

Indexes

@@index([categoryId, isActive])
@@index([isActive, name])

Index the columns you filter and sort by, not every column. Every index makes writes slower and takes space.

Column order in a composite index matters: [categoryId, isActive] helps a query filtering on categoryId alone or on both, but not one filtering on isActive alone. Put the most selective column first.

Verify rather than guess:

EXPLAIN ANALYZE
SELECT * FROM products WHERE "categoryId" = 'x' AND "isActive" = true;

Index Scan is good. Seq Scan on a large table means the index is not being used.

Note the double quotes. @@map("products") renames the table, and nothing renames the columns — Prisma leaves those as you wrote them, in camelCase. PostgreSQL folds unquoted identifiers to lowercase, so categoryId becomes categoryid and the query fails with "column does not exist".

Quoting is the fix. Add @map("category_id") per field if you want fully snake_case columns, which is the PostgreSQL convention and about sixty lines of annotation. We have not, because with Prisma you write raw SQL rarely — but knowing why the quotes are there saves a confusing ten minutes the first time.

What we are leaving out, deliberately

Naming it is part of the design:

Product options beyond size — colour, flavour. Variants would need an attributes table. Groceries rarely need it.

Weight-adjusted pricing — charging for the actual 1.05kg. Real, and it complicates orders enough to be out of scope.

Multiple warehouses. One shop, one stock number.

Price history. We keep the price on the order line, which is what matters. A full audit of price changes is a separate table.

Write down what you excluded and why. A schema that quietly cannot express something is worse than one that says so.

A worked example

Category: Staples
  Product: Aashirvaad Select Atta
    Variant: 5kg pack    — SKU AAS-ATTA-5   ₹285.00  stock 12
    Variant: 10kg pack   — SKU AAS-ATTA-10  ₹560.00  stock 4

Category: Vegetables
  Product: Tomato
    Variant: 500g        — SKU VEG-TOM-500  ₹25.00   stock 40
    Variant: 1kg         — SKU VEG-TOM-1000 ₹48.00   stock 25

Note the 1kg tomatoes are cheaper per gram than the 500g, which is normal and which a single price-per-product model could not express.

Check your work

Why variants are separate rows: one product can be sold in several sizes with different prices and stock, without duplicating its description, image and category.

Where price and stock belong: on the variant. On the product they cannot express two sizes.

Why unit is an enum: free text fills with inconsistent spellings within weeks.

Why Decimal for quantity but Int for price: quantity is a measurement needing fractions; money must be exact, so it is an integer count of paise.

Why mrpPaise is stored rather than a discount percentage: recomputing the original price from a percentage introduces rounding errors.

Why isActive rather than deleting: old orders reference the product, and deleting leaves history showing unknown items.

The cost of soft delete: every query must filter, and forgetting shows inactive products in the shop.

Why raw SQL needs quoted column names: @@map renames the table only. Columns stay camelCase, and PostgreSQL lowercases unquoted identifiers.

What column order in a composite index means: the index helps queries filtering on the leading column, or the leading columns together — not on a later column alone.

Practice

1. Write the full schema and migrate it.
2. Add two categories, two products and four variants through Prisma Studio.
3. Write a query returning active products with their active variants.
4. Deactivate a product and confirm your query excludes it. Then write a query that forgets the filter and see it appear.
5. Try creating two variants with the same SKU. Read the error.
6. Try creating a variant with unit: "kg" as a string and read the enum error.
7. Delete a product with variants and confirm the cascade removed them.
8. Remove onDelete: Cascade, migrate, and try again. Read the foreign key error.
9. Run EXPLAIN ANALYZE on a category query with and without the index.
10. Write down three things this schema cannot express, and say for each whether that is acceptable.

Next: relations, and how to query across them without the N+1 problem.