Part 3: Schema Design, Relationships, and Migrations

Introduction

In Part 2, we set up our development environment and created a basic schema. Now comes the challenging part: designing a schema that will serve your application well as it grows. I learned this lesson the hard way in an e-commerce project where poor initial schema design led to weeks of painful migrations and data transformations.

In this article, I'll share the schema design principles I follow, how to model different types of relationships, and how to manage schema evolution through migrations. We'll build on the foundation from Part 2 using TypeScript, Prisma, and PostgreSQL.

Schema Design Principles

Principle 1: Start with Your Domain, Not Your Database

When I first started, I'd think about tables and columns. Now, I think about entities and their relationships in my application domain.

For a blog application I built, I started by mapping out:

• Users can write Posts

• Posts can have Comments

• Posts can have multiple Tags

• Users can follow other Users

Only after understanding these relationships did I design the schema.

Principle 2: Use Descriptive, Consistent Naming

Here's the naming convention I follow:

// ✅ Good: Clear, consistent naming
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  firstName String   // camelCase for fields
  lastName  String
  createdAt DateTime @default(now())  // Consistent timestamp naming
  updatedAt DateTime @updatedAt
}

// ❌ Avoid: Inconsistent, unclear naming
model user {  // Models should be PascalCase
  ID        Int      @id
  mail      String   // Unclear abbreviation
  fname     String   // Confusing abbreviation
  create_time DateTime  // Inconsistent with updatedAt
}

Principle 3: Think About Queries You'll Run

Before finalizing a schema, I list common queries. For a blog:

// Common queries I need to support:
// 1. Get all posts by a user (need userId on Post)
// 2. Get all comments on a post (need postId on Comment)
// 3. Find posts by tag (need many-to-many relationship)
// 4. Get trending posts (need view count, created date)

This helps me decide on relationships and indexes.

Modeling Relationships

One-to-Many Relationships

The most common relationship type. Example: One user has many posts.

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // One-to-many: One user has many posts
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Foreign key
  authorId  Int
  // Relation field
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

Key Points:

• posts Post[] on User is the relation field (not stored in database)

• authorId is the foreign key (actual column in database)

• @relation(...) defines the relationship constraints

• onDelete: Cascade means deleting a user deletes their posts

One-to-One Relationships

Less common but useful. Example: User has one profile with extended information.

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  
  // One-to-one relationship
  profile   Profile?
}

model Profile {
  id        Int      @id @default(autoincrement())
  bio       String?
  avatar    String?
  website   String?
  
  // Foreign key for one-to-one
  userId    Int      @unique  // @unique makes it one-to-one
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Key Difference:

• profile Profile? (with ?) means optional one-to-one

• userId Int @unique ensures one profile per user

Many-to-Many Relationships

Most complex but powerful. Example: Posts can have many tags, tags can be on many posts.

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  
  // Many-to-many relationship
  tags      Tag[]
}

model Tag {
  id    Int      @id @default(autoincrement())
  name  String   @unique
  
  // Many-to-many relationship
  posts Post[]
}

Prisma automatically creates a join table _PostToTag. To see it:

npx prisma migrate dev --name add_tags

Generated SQL includes:

-- CreateTable
CREATE TABLE "_PostToTag" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL
);

-- CreateIndex
CREATE UNIQUE INDEX "_PostToTag_AB_unique" ON "_PostToTag"("A", "B");
CREATE INDEX "_PostToTag_B_index" ON "_PostToTag"("B");

-- AddForeignKey
ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_A_fkey" 
  FOREIGN KEY ("A") REFERENCES "Post"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_B_fkey" 
  FOREIGN KEY ("B") REFERENCES "Tag"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Explicit Many-to-Many (When You Need Extra Fields):

In an e-commerce project, I needed to track when a product was added to a category:

model Product {
  id         Int                @id @default(autoincrement())
  name       String
  categories ProductCategory[]
}

model Category {
  id       Int                @id @default(autoincrement())
  name     String
  products ProductCategory[]
}

// Explicit join table with extra fields
model ProductCategory {
  product    Product   @relation(fields: [productId], references: [id])
  productId  Int
  
  category   Category  @relation(fields: [categoryId], references: [id])
  categoryId Int
  
  // Extra fields
  addedAt    DateTime  @default(now())
  featured   Boolean   @default(false)
  
  @@id([productId, categoryId])  // Composite primary key
}

Self-Referencing Relationships

For features like user follows. This one took me a while to grasp:

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  
  // Users this user follows
  following Follow[] @relation("UserFollows")
  
  // Users following this user (followers)
  followers Follow[] @relation("UserFollowers")
}

model Follow {
  id          Int      @id @default(autoincrement())
  
  follower    User     @relation("UserFollows", fields: [followerId], references: [id])
  followerId  Int
  
  following   User     @relation("UserFollowers", fields: [followingId], references: [id])
  followingId Int
  
  createdAt   DateTime @default(now())
  
  @@unique([followerId, followingId])  // Can't follow same person twice
}

Complete Blog Schema Example

Here's a complete schema from a blog system I built, demonstrating all relationship types:

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

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

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  password  String
  role      Role      @default(USER)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  
  // Relationships
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  
  @@index([email])  // Index for faster lookups
}

model Profile {
  id       Int     @id @default(autoincrement())
  bio      String?
  avatar   String?
  website  String?
  
  userId   Int     @unique
  user     User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String    @unique
  content     String
  excerpt     String?
  published   Boolean   @default(false)
  viewCount   Int       @default(0)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  publishedAt DateTime?
  
  // Foreign key
  authorId    Int
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  
  // Relationships
  comments    Comment[]
  tags        Tag[]
  
  @@index([authorId])
  @@index([published, createdAt])  // Composite index for filtering published posts
  @@index([slug])
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Foreign keys
  postId    Int
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  
  @@index([postId])
  @@index([authorId])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  slug  String @unique
  
  posts Post[]
  
  @@index([slug])
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Understanding Migrations

What Migrations Do

Migrations are version control for your database schema. Each migration is a set of SQL commands that transform your database from one state to another.

Creating Migrations

Workflow I follow:

# 1. Modify schema.prisma
# 2. Create migration
npx prisma migrate dev --name descriptive_name

# Examples of good migration names:
# - add_user_roles
# - create_comments_table
# - add_post_view_count
# - make_email_unique

Migration Best Practices

1. Small, Focused Migrations

# ❌ Bad: Too many changes in one migration
npx prisma migrate dev --name update_schema

# ✅ Good: Specific, focused migrations
npx prisma migrate dev --name add_profile_model
npx prisma migrate dev --name add_post_tags
npx prisma migrate dev --name add_view_count_to_posts

2. Never Edit Applied Migrations

Once a migration is applied, never modify it. Instead, create a new migration:

# If you made a mistake, create a corrective migration
npx prisma migrate dev --name fix_user_email_constraint

3. Review Generated SQL

Always check the generated SQL before deploying:

# Check migration SQL
cat prisma/migrations/20240204_add_tags/migration.sql

Handling Schema Changes

Adding a Required Field to Existing Table:

This is tricky because existing records don't have values. Here's how I handle it:

// Step 1: Add field as optional
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  username  String?  // Optional first
}

npx prisma migrate dev --name add_username_optional

// Step 2: Backfill data
const users = await prisma.user.findMany();
for (const user of users) {
  await prisma.user.update({
    where: { id: user.id },
    data: { username: user.email.split('@')[0] },  // Generate username from email
  });
}

// Step 3: Make field required
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  username  String   @unique  // Now required and unique
}

npx prisma migrate dev --name make_username_required

Renaming Fields:

Prisma can't automatically detect renames, so it drops and recreates:

// Want to rename 'name' to 'fullName'
model User {
  id       Int    @id @default(autoincrement())
  fullName String  // Prisma sees this as new field
}

To preserve data, use raw SQL:

# Create empty migration
npx prisma migrate dev --create-only --name rename_name_to_fullName

Edit the generated migration file:

-- Rename column instead of dropping
ALTER TABLE "User" RENAME COLUMN "name" TO "fullName";

# Apply migration
npx prisma migrate dev

Indexes for Performance

In a project with millions of posts, queries were slow until I added indexes:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  authorId  Int
  published Boolean
  createdAt DateTime @default(now())
  
  author    User     @relation(fields: [authorId], references: [id])
  
  // Single column indexes
  @@index([authorId])        // Fast lookup by author
  @@index([createdAt])       // Fast sorting by date
  
  // Composite index
  @@index([published, createdAt])  // Fast filtering published + sorting
  
  // Full-text search (PostgreSQL specific)
  @@index([title], type: Hash)
}

When to Add Indexes:

• Foreign keys (Prisma doesn't auto-index these)

• Fields frequently used in WHERE clauses

• Fields used for sorting (ORDER BY)

• Composite indexes for common query patterns

When NOT to Add Indexes:

• Every column (indexes slow down writes)

• Small tables (< 1000 rows)

• Columns rarely queried

Migration Workflow for Teams

Working with a team requires discipline:

# 1. Pull latest code
git pull

# 2. Apply any new migrations teammates created
npx prisma migrate dev

# 3. Make your schema changes
# Edit prisma/schema.prisma

# 4. Create your migration
npx prisma migrate dev --name my_changes

# 5. Commit everything
git add prisma/
git commit -m "Add user profiles"
git push

Resetting Your Database

During development, sometimes you need a fresh start:

# ⚠️ DANGER: Deletes all data
npx prisma migrate reset

# What it does:
# 1. Drops database
# 2. Creates new database
# 3. Applies all migrations
# 4. Runs seed script (if configured)

Never run this in production!

What's Next?

We now understand how to design schemas, model relationships, and manage migrations. In Part 4, we'll put this knowledge to use:

• CRUD operations with Prisma Client

• Querying related data

• Filtering and sorting

• Pagination

• Transactions

Conclusion

Schema design is both an art and a science. The principles I've shared come from years of mistakes and learning. Start simple, iterate based on your application's needs, and use migrations to evolve your schema safely.

The schema we designed here supports a fully functional blog application. It demonstrates one-to-many (User → Posts), one-to-one (User → Profile), many-to-many (Posts ↔ Tags), and proper indexing for performance.

---

This article is part of my personal knowledge sharing based on real projects I've built. The schema patterns shown here are used in production applications handling significant traffic.