Database Design

Database Design Skill

Purpose: Design efficient, normalized database schemas

Overview

This skill provides guidance for designing performant, scalable database schemas following best practices.

Schema Design Principles

1. Normalization (3NF)

• 1NF: Atomic values, no repeating groups
• 2NF: No partial dependencies
• 3NF: No transitive dependencies

2. Naming Conventions

-- Tables: plural, snake_case
users, order_items, user_preferences

-- Columns: singular, snake_case
user_id, created_at, is_active

-- Primary Keys: id or {table}_id
id, user_id

-- Foreign Keys: {referenced_table}_id
user_id, order_id

Common Patterns

User Entity

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  password  String
  firstName String?
  lastName  String?
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

enum Role {
  USER
  ADMIN
}

One-to-Many

model User {
  id     String  @id @default(uuid())
  orders Order[]
}

model Order {
  id     String @id @default(uuid())
  userId String
  user   User   @relation(fields: [userId], references: [id])
}

Many-to-Many

model User {
  id    String @id @default(uuid())
  roles Role[]
}

model Role {
  id    String @id @default(uuid())
  users User[]
}

Indexing Strategy

model User {
  id    String @id @default(uuid())
  email String @unique

  @@index([createdAt])
  @@index([isActive, role])
}

When to Index

Query Optimization

// N+1 Problem
const users = await prisma.user.findMany();
for (const user of users) {
  const orders = await prisma.order.findMany({ where: { userId: user.id } });
}

// Eager Loading
const users = await prisma.user.findMany({
  include: { orders: true },
});

CAP Theorem

In a distributed system, you can guarantee at most two of three properties simultaneously:

• Consistency (C): Every read returns the most recent write
• Availability (A): Every request receives a response (no timeout)
• Partition Tolerance (P): The system continues operating despite network partitions

Since network partitions are unavoidable in distributed systems, the real choice is between CP and AP.

Decision Matrix

ACID vs BASE

Property Comparison

When to Use Each

• ACID: Money movement, order processing, anything requiring rollback guarantees, regulatory compliance
• BASE: High-throughput writes, geographically distributed reads, systems where stale reads are acceptable for seconds

Consistency Models

From strongest to weakest, choose the level your application actually needs:

Choose the weakest model your application can tolerate to maximize performance and availability.

Migration Safety

Zero-Downtime Migration Pattern

Safe migrations follow a multi-phase approach that avoids locking tables or breaking running application code.

Phase 1 - Expand: Add new structures alongside old ones Phase 2 - Migrate: Backfill data, dual-write to both structures Phase 3 - Contract: Remove old structures after all consumers have switched

Safe vs Unsafe Operations

Backfill Pattern

// Backfill in batches to avoid long-running transactions
async function backfillNewColumn(batchSize = 1000) {
  let processed = 0;
  let hasMore = true;

  while (hasMore) {
    const rows = await prisma.$executeRaw`
      UPDATE users
      SET display_name = first_name || ' ' || last_name
      WHERE display_name IS NULL
      LIMIT ${batchSize}
    `;

    processed += rows;
    hasMore = rows === batchSize;

    // Yield to other operations between batches
    await new Promise((resolve) => setTimeout(resolve, 100));
  }

  return processed;
}

Connection Pooling

Pool Size Guidance

Sizing Formula

max_pool_size = (db_max_connections - reserved_superuser_connections) / number_of_app_instances

For PostgreSQL with max_connections = 100, 3 superuser slots reserved, and 4 app instances: (100 - 3) / 4 = ~24 connections per instance

Tool Recommendations

// Prisma connection pool configuration
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL") // ?connection_limit=20&pool_timeout=10
}

Quick Reference