Designing Production-Grade Models With Prisma (Beyond CRUD)

In Part 1, we talked about why schemas fail quietly in production.

Now we’re going to talk about the part that makes most developers uncomfortable:
relationships.

Not because relationships are technically hard.

Because most people ask the wrong questions.

The question everyone asks (and why it’s wrong)

When developers model relationships, they usually ask:

• “Should this be one-to-many or many-to-many?”

• “Where do I put the foreign key?”

• “Do I need a join table?”

There are implementation questions.

They come second.

The real question—the one that determines whether your schema survives production—is:

“Who owns this data, and what must never be allowed to happen?”

Answer that first, and the rest becomes obvious.

Ownership is not about foreign keys

Ownership is about responsibility.

Let me show you what I mean.

Look at this relationship:

model Order {
  id String @id

  shippingAddress ShippingAddress?
}

model ShippingAddress {
  id String @id

  orderId String @unique
  order Order @relation(fields: [orderId], references: [id])
}

Technically, ShippingAddress holds the foreign key.

But Order owns the relationship.

Why?

Because a ShippingAddress without an Order is meaningless.
The shipping address exists because the order exists.
If you delete the order, the shipping address loses its reason to exist according to our relationship.

This is ownership.

The owner defines the lifecycle. The owned follows.

One-to-one relationships: The illusion of simplicity

One-to-one relationships feel simple until you ship them.

Then you realize: one-to-one is actually the trickiest.

Because there are two ways to model one-to-one, and most people pick the wrong one.

The wrong way

model Order {
  id String @id

  paymentId String? @unique
  payment Payment? @relation(fields: [paymentId], references: [id])
}

model Payment {
  id String @id
  order Order?
}

This says: “An order might have a payment.”

But that’s not how e-commerce works.

An order must have a payment. Otherwise it’s not an order—it’s a cart.

Making the relationship optional means your database accepts impossible states.

The right way

model Order {
  id String @id

  payment Payment?
}

model Payment {
  id String @id

  orderId String @unique
  order Order @relation(fields: [orderId], references: [id], onDelete: Restrict)
}

Now the foreign key lives in Payment.

Why?

Because Payment depends on Order.

A payment without an order is dangerous.

An order without a payment (temporarily, before checkout completes) is normal.

The foreign key goes in the dependent model.

The rule for one-to-one

Ask yourself: “Which side cannot exist independently?”

That’s where the foreign key goes.

In our schema:

• ShippingAddress cannot exist without Order → foreign key in ShippingAddress.

• Payment cannot exist without Order → foreign key in Payment.

One-to-many: The most common relationship (and how we still mess it up)

One-to-many feels intuitive.

A User has many Orders. An Order has many OrderItems.

But here’s where we stumble: we make the relationship optional when it shouldn’t be.

The anti-pattern

model OrderItem {
  id String @id

  orderId String?
  order Order? @relation(fields: [orderId], references: [id])
}

An OrderItem without an Order is not just wrong.

It’s financially dangerous.

It means you have line items floating in your database with no parent transaction.

No audit trail. No owner. No explanation.

This is how financial data disappears.

The correct model

model OrderItem {
  id String @id

  orderId String
  order Order @relation(fields: [orderId], references: [id], onDelete: Restrict)
}

Every OrderItem must belong to an Order.

No exceptions.

If you can’t find the order, you can’t create the item.

This is a business rule, not a preference.

Why Restrict matters

Notice the onDelete: Restrict .

This says: “You cannot delete an Order if OrderItems exist.”

Why?

Because deleting an order while items exist would break your financial audit.

You’d have orphaned line items pointing to nothing.

Restrict forces you to handle the items first—either by deleting them intentionally or by moving them elsewhere.

This is not pedantic. This is how you prevent data loss in production.

Why OrderItem exists (and you can’t just use Product)

This trips up almost everyone.

“Why do I need OrderItem? Can’t I just reference Product directly from Order?

No.

And here’s why.

Products are mutable. Orders are immutable.

A product’s price can change.

A product’s name can change.

A product can be deleted.

But an order is a historical record.

When someone bought a “Blue T-shirt” or KSH1200 on March 10th 202, that fact must never change—even if:

• The product is renamed to “Hooli T-shirt”

• The price increases to KSH2000

• The product is deleted entirely

If you reference Product directly, your orders become unstable.

The solution: Snapshot the product data

model OrderItem {
  id String @id

  productName String
  productSku String
  productDescription String?
  price Decimal

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

  orderId String
  order Order @relation(fields: [orderId], references: [id], onDelete: Restrict)
}

Notice:

• We store productName, productSku, price directly in OrderItem

• We make productId optional

• We use onDelete: SetNull on the product reference

This means:

1. When an order is created, we copy the product data into the OrderItem

2. If the product gets deleted later, we set productId to null

3. But the snapshot data remains.

The order still knows what was purchased.

This is the difference between live data and historical data.

Cart vs Order: Why they must never be the same thing

This is one of the most dangerous mistakes in e-commerce schemas.

Treating a cart and an order as the same entity.

They are not.

A Cart is:

• Mutable

• Temporary

• Disposable

• Represents intent, not commitment

An Order is:

• Immutable

• Permanent

• A financial record

• Represents a completed transaction

Mixing them is like using the same table for “drafts” and “published posts”.

It works until it doesn’t.

What happens when you mix them

If you try to use a single Order model with a status field:

model Order {
  id String @id
  status OrderStatus // DRAFT, PENDING, COMPLETED
}

enum OrderStatus {
  DRAFT      // This is actually a cart
  PENDING
  COMPLETED
}

You end up with:

• Financial records that can be edited

• Audit trails that disappear

• Race conditions when converting “drafts” to orders

• Confusion about when data becomes immutable

The correct model: Separate them entirely

model Cart {
  id String @id
  items CartItem[]

  userId String
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Order {
  id String @id
  orderNumber String @unique
  status OrderStatus

  userId String
  user User @relation(fields: [userId], references: [id], onDelete: Restrict)
}

Notice the difference in deletion strategies:

• Cart → onDelete: Cascade (disposable)

• Order → onDelete: Restrict (permanent)

This is intentional.

If a user gets deleted:

• Their cart disappears (it's temporary state)

• Their orders remain (they're financial records)

The mental model

Think of it this way:

Cart = Shopping basket in a store
Order = Receipt after checkout

You don't keep the basket.
You keep the receipt.

Many-to-many: The relationship everyone over-complicated

Many-to-many relationships scare people.

But they’re actually straightforward once you understand ownership.

The question is not “How do I create a join table?”

The question is: “What data lives in the relationship itself?”

Implicit many-to-many (Prisma does it for you)

model Product {
  categories Category[]
}

model Category {
  products Product[]
}

This works when the relationship has no additional data.

Prisma creates a join table automatically.

Use this when: The relationship is pure.

Explicit many-to-many (You need a join model)

But what if you need to store data about the relationship?

Like in our case: a cart item isn’t just “product in cart”.

It’s “product in cart with a specific quantity”.

model Cart {
  items CartItem[]
}

model Product {
  cartItems CartItem[]
}

model CartItem {
  id String @id
  quantity Int

  cartId String
  cart Cart @relation(fields: [cartId], references: [id], onDelete: Cascade)

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

  @@unique([cartId, productId, deletedAt])
}

Notice:

• CartItem is the join model

• It holds quantity (data about the relationship)

• It has foreign keys to both Cart and Product

• The @@unique constraint prevents duplicate entries

This is how you prevent:

• Adding the same product twice to a cart

• Race conditions during cart updates

When to use which

Use implicit many-to-many when:

• The relationship has no additional data

• You just need to connect two entities

Use explicit many-to-many when:

• You need to store data about the relationship

• You need to query the relationship itself

• You need constraints on the join table

The invariants hiding in your relationships

Every relationship encodes business rules.

Most people don’t see them.

Here are the invariants in our schema:

Order →User

• Invariant: “Every order must belong to a user”

• Enforced by: userId String (required, not nullable)

OrderItem → Order

• Invariant: “An order item cannot exist without its parent order”

• Enforced by: “orderId String + onDelete: Restrict

Payment → Order

• Invariant: "A payment must reference an order"

• Enforced by: orderId String @unique

Cart → User

• Invariant: "A user can have only one active cart"

• Enforced by: @@unique([userId, deletedAt])

CartItem → Product

• Invariant: "A cart cannot contain the same product twice"

• Enforced by: @@unique([cartId, productId, deletedAt])

These aren’t just constraints.

They’re promises your schema makes to the business.

The deletion strategy matrix

Different relationships require different deletion strategies.

Here's the decision tree:

Notice the pattern:

• Cascade = “This data is temporary or regenerable.””

• Restrict \= “This data is critical; handle it explicitly”

• SetNull = “Keep the record, lose the reference

"But this feels over-engineered"

I hear this a lot.

"Why can't I just make everything nullable and handle it in code?"

Because you can't handle race conditions in code.

You can't prevent two API requests from creating duplicate carts in code.

You can't stop an accidental bulk delete from wiping financial history in code.

Databases exist to enforce invariants even when your code misbehaves.

Your application is one client.

Tomorrow, you might add:

• A background job

• A data import script

• An admin panel

• A mobile app

If the rules only exist in one codebase, they will be violated.

What's next

In Part 3, we'll tackle the most expensive mistake in production databases:

Soft Deletes Explained: Why onDelete: Cascade Can Destroy Your Data

We'll cover:

• The catastrophic incompatibility between soft deletes and cascades

• Why financial data and ephemeral data need opposite strategies

• Real production horror stories (the kind that end with "all hands" Slack messages)

• A decision matrix showing exactly when cascades are safe vs dangerous

• Why deleting a ProductImage is fine but deleting an OrderItem is a fireable offense

Closing thought

Relationships aren't about connecting tables.

They're about encoding reality.

When you model a relationship, you're making a promise:

"This thing depends on that thing, and this dependency will be protected."

Get the ownership right, and the schema writes itself.

Get it wrong, and you'll be migrating data at 2 AM wondering where it all went wrong.