Data modeling best practices

constraints. Use foreign keys for referential integrity, unique constraints for natural keys, and check constraints for business rules. Avoid nullable columns unless the absence of data is semantically distinct from a default value.

Cardinality Rules:

• 1:1: Use shared primary keys or unique foreign keys.
• 1:N: Foreign key on the "many" side.
• M:N: Junction table with composite primary key and additional attributes if necessary.

Step 3: Type-Safe Implementation with TypeScript

Modern data modeling requires the schema to be the single source of truth for types. Use tools like Prisma, TypeORM, or Drizzle to generate TypeScript interfaces directly from the database schema. This eliminates manual type maintenance and ensures compile-time validation.

Code Example: Type-Safe Schema Definition

This example uses Prisma schema syntax to define a robust model, followed by TypeScript usage patterns that enforce best practices like branded types for IDs.

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

// Best Practice: Use UUIDs for distributed systems, Integers for high-volume internal logs.
// Best Practice: Add comments for documentation generation.

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  profile   Profile?
  orders    Order[]

  // Best Practice: Include indexes on frequently queried columns
  @@index([email])
}

model Profile {
  id        String @id @default(uuid())
  userId    String @unique
  bio       String?
  avatarUrl String?
  user      User   @relation(fields: [userId], references: [id])
}

model Order {
  id        String   @id @default(uuid())
  userId    String
  status    OrderStatus @default(PENDING)
  totalCents Int     // Best Practice: Store currency as integers to avoid float precision issues
  createdAt DateTime @default(now())
  
  user      User     @relation(fields: [userId], references: [id])
  items     OrderItem[]

  // Best Practice: Composite index for dashboard queries
  @@index([userId, createdAt])
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  CANCELLED
}

model OrderItem {
  id        String @id @default(uuid())
  orderId   String
  productId String
  quantity  Int
  priceCents Int
  
  order     Order  @relation(fields: [orderId], references: [id])
  
  @@id([orderId, productId]) // Best Practice: Natural composite key for junction
}

TypeScript Usage with Branded Types:

Branded types prevent accidental mixing of ID types across entities, a common source of runtime bugs.

import { PrismaClient } from '@prisma/client';

// Branded Types for Compile-Time Safety
type UserId = string & { readonly __brand: 'UserId' };
type OrderId = string & { readonly __brand: 'OrderId' };

const prisma = new PrismaClient();

// Best Practice: Explicitly select fields to avoid over-fetching
async function getUserDashboard(userId: UserId) {
  return prisma.user.findUnique({
    where: { id: userId },
    select: {
      email: true,
      profile: {
        select: { bio: true, avatarUrl: true }
      },
      orders: {
        where: { status: { in: ['PAID', 'SHIPPED'] } },
        orderBy: { createdAt: 'desc' },
        take: 10,
        select: {
          id: true,
          totalCents: true,
          createdAt: true,
          items: {
            select: { quantity: true, priceCents: true }
          }
        }
      }
    }
  });
}

// Usage
// const dashboard = await getUserDashboard("some-string" as UserId); // Compile error if not branded

Step 4: Indexing Strategy and Query Optimization

Indexes are not an afterthought. Define indexes based on the access patterns identified in Step 1.

• Single Column Indexes: For high-selectivity filters.
• Composite Indexes: For queries filtering on multiple columns. Order columns by selectivity (most selective first) and include ORDER BY columns to avoid filesorts.
• Covering Indexes: Include all columns required by a query to enable index-only scans.

Step 5: Migration and Evolution

Schema changes must be versioned and idempotent. Use migration tools to manage DDL changes. Never alter production schemas manually. Implement a strategy for backward-compatible changes (expand/contract pattern) to support zero-downtime deployments.

Pitfall Guide

1. Over-Normalization for Read-Heavy Workloads

Mistake: Applying 3NF rigorously to data accessed primarily via complex joins. Impact: Query latency increases exponentially as data grows. Joins become expensive, requiring application-side pagination or caching layers to mask performance issues. Best Practice: Denormalize aggressively for read paths. Store computed aggregates (e.g., totalCents on Order) and duplicate read-only attributes (e.g., productName on OrderItem) when the cost of duplication is lower than the cost of the join.

2. JSON Dumping Without Structure

Mistake: Using JSON/JSONB columns to store structured data without defining the schema or indexing internal fields. Impact: Loss of type safety, inability to enforce constraints, and degraded query performance. The database becomes a black box. Best Practice: Use JSON only for truly polymorphic or unstructured attributes. Define generated columns for indexed JSON paths and enforce schema validation at the application layer.

3. Missing Soft Delete Implementation

Mistake: Using hard deletes (DELETE) for critical business entities. Impact: Broken referential integrity, loss of audit trails, and inability to recover from accidental deletions. Hard deletes also complicate data replication and backup strategies. Best Practice: Implement soft deletes using a deletedAt timestamp. Add partial indexes to exclude soft-deleted rows from query plans. Ensure foreign key constraints handle soft-deleted records appropriately.

4. Index Bloat and Missing Selectivity Analysis

Mistake: Creating indexes on every column or low-selectivity columns (e.g., boolean flags with 50/50 distribution). Impact: Write performance degradation due to index maintenance overhead. Increased storage usage. Query planner confusion leading to suboptimal execution plans. Best Practice: Analyze query plans and index selectivity. Only index columns used in WHERE, JOIN, ORDER BY, or GROUP BY clauses with high cardinality. Regularly review and remove unused indexes.

5. Timestamp Ambiguity and Timezone Mismanagement

Mistake: Storing timestamps without timezone information or mixing local time with UTC. Impact: Data inconsistency across regions, incorrect sorting, and bugs in time-based logic (e.g., scheduling, TTL). Best Practice: Store all timestamps in UTC. Use TIMESTAMPTZ in PostgreSQL. Convert to local time only at the presentation layer.

6. Surrogate Key Obsession

Mistake: Introducing surrogate keys for every entity, even when a natural key exists and is immutable. Impact: Unnecessary storage overhead and complexity. Natural keys (e.g., email, sku) are often more meaningful and reduce join complexity. Best Practice: Use natural keys when they are stable, unique, and reasonably sized. Use surrogate keys for high-volume tables, composite natural keys, or when natural keys may change.

7. Treating Database as a JSON Blob Store

Mistake: Bypassing relational features and constraints by storing entire object graphs in a single column. Impact: Inability to query relationships efficiently, data integrity risks, and difficulty in scaling. Best Practice: Model relationships explicitly. Use the database for what it does best: enforcing constraints and querying structured data. Use document stores only when the data model is inherently hierarchical and access patterns are document-centric.

Production Bundle

Action Checklist

• Map Access Patterns: Document all read/write paths with expected volume and latency requirements before designing the schema.
• Enforce Constraints: Apply foreign keys, unique constraints, and check constraints at the database level. Do not rely solely on application validation.
• Implement Type Safety: Generate TypeScript types directly from the schema. Use branded types for IDs to prevent cross-entity errors.
• Optimize Indexes: Create indexes based on access patterns. Review query plans to ensure indexes are utilized. Remove unused indexes.
• Handle Currency Correctly: Store monetary values as integers (cents) or decimals. Never use floating-point types for currency.
• Plan for Evolution: Use versioned migrations. Implement expand/contract patterns for zero-downtime schema changes.
• Audit and Soft Deletes: Implement createdAt, updatedAt, and deletedAt for all business entities. Enable audit logging for sensitive data.
• Review Cardinality: Validate 1:1, 1:N, and M:N relationships. Use junction tables with composite keys for M:N relations.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High Read Throughput, Complex Relations	Access-Driven Relational (PostgreSQL)	Optimized joins, type safety, and ACID compliance with denormalization for read paths.	Medium infra, Low dev cost.
Flexible Schema, High Write Volume	Document Store (MongoDB/DynamoDB)	Schema flexibility allows rapid iteration. Sharding handles write scale.	Low dev cost, High infra cost at scale.
Deep Relationship Queries, Graph Data	Graph Database (Neo4j)	Native relationship traversal outperforms joins for highly connected data.	High infra cost, Specialized dev skills.
Real-Time Analytics, Time-Series	Time-Series DB (TimescaleDB)	Optimized storage and compression for append-only time-series data.	Medium infra, Low query cost.
Multi-Region, Low Latency Reads	Distributed SQL (CockroachDB/Yugabyte)	Global distribution with strong consistency. Automatic sharding.	High infra cost, Low operational overhead.

Configuration Template

Copy this Prisma schema template to establish a robust foundation for a new project. It includes best practices for types, constraints, and indexing.

// schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

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

// Base model mixin for audit fields
model BaseModel {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?
  
  @@index([deletedAt]) // Partial index support via where clause in queries
}

model User extends BaseModel {
  email     String   @unique
  role      UserRole @default(USER)
  
  @@index([role])
  @@map("users")
}

enum UserRole {
  USER
  ADMIN
}

model Product extends BaseModel {
  sku       String   @unique
  name      String
  priceCents Int
  stock     Int      @default(0)
  
  // Best Practice: Full-text search index
  @@index([name(sort: Desc)])
  @@map("products")
}

// Junction table with extra attributes
model OrderItem {
  orderId   String
  productId String
  quantity  Int
  unitPriceCents Int
  
  order     Order    @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product   Product  @relation(fields: [productId], references: [id])
  
  @@id([orderId, productId])
  @@map("order_items")
}

Quick Start Guide

1. Initialize Project:

   npm init -y
   npm install prisma @prisma/client
   npx prisma init
   

2. Define Schema: Create prisma/schema.prisma and paste the configuration template. Modify entities to match your domain.

3. Generate Types and Client:

   npx prisma generate
   

   This creates type-safe TypeScript interfaces and the Prisma Client.

4. Run Migration:

   npx prisma migrate dev --name init
   

   This creates the database schema and a migration file.

5. Implement Service Layer: Use the generated client in your TypeScript services. Enforce branded types and access patterns defined in your design phase.

   import { PrismaClient } from '@prisma/client';
   const db = new PrismaClient();
   // Use db with type safety