database-schema-design

name	Database Schema Design
description	DESIGN normalized, scalable relational database schema before writing backend code. Enforce 3NF, UUIDv7 primary keys, foreign keys with cascade rules, and query indexes. Prevent denormalized data, missing constraints, and unindexed queries. Trigger: "design (the|a) database", "create (the|a) schema", "set up (Prisma|Drizzle)", "plan (the|the) tables".
category	backend
version	3.0.0
last_updated	2026-06-28T00:00:00.000Z
stacks	["PostgreSQL 17","Prisma 7","Drizzle ORM 0.45+","MySQL","SQLite"]
triggers	[{"pattern":"design (the|a) (database|schema|tables)","action":"RUN schema design workflow"},{"pattern":"set up (Prisma|Drizzle)","action":"RUN schema design workflow"},{"pattern":"plan (the|the) tables","action":"RUN schema design workflow"},{"pattern":"project requires data persistence","action":"RUN schema design before any API route code"}]
related_skills	["api-route-structure","backend-validation-layers","saas-app-structure"]

Database Schema Design

IDENTIFY: When to Activate

Activate when:

• Planning a new feature that persists complex relational data
• User says "design the database", "create tables", "set up Prisma/Drizzle"
• During architectural planning phase (after requirements discovery)

DECIDE: Schema Design Path

IF greenfield project →
  RUN Steps 1-7 in order

IF adding tables to existing schema →
  SCAN existing schema first (schema.prisma or drizzle schema)
  FOLLOW existing conventions (UUID vs auto-increment, naming style, timestamps)
  RUN Steps 1-7, but skip decisions already made by existing schema

IF NoSQL required (document-based, key-value) →
  STOP, this skill is for relational databases only
  Use JSONB with caution (see EXECUTE Step 8)

EXECUTE: Instructions

Step 1: Identify Entities

List every distinct "thing" the application tracks. Each becomes a table.

IDENTIFICATION RULES:
- Each entity is a noun: User, Order, Product, Comment, Tag
- Each entity gets ONE table
- If a noun cannot exist independently → it's an attribute, not an entity

OUTPUT FORMAT:
[Table]: [columns], [purpose]
Example:
users: id, email, name, created_at, updated_at, system users
posts: id, title, content, author_id, published_at, created_at, updated_at, blog posts

Step 2: Define Relationships

Map connections between entities:

RELATIONSHIP TYPES:
1:1  → Foreign key on either side + UNIQUE constraint
1:N  → Foreign key on the "many" side (post.comment_id → comment.id)
N:M  → Bridge table (post_tags: post_id + tag_id composite PK)

DIAGRAM FORMAT:
[TableA] [N]───[N] [TableB]    , many-to-many (N:M)
[TableA] 1───N [TableB]        , one-to-many (1:N)
[TableA] 1───1 [TableB]        , one-to-one (1:1)

Step 3: Choose Primary Keys

DEFAULT: UUIDv7 (time-ordered, B-tree friendly, distributed-safe)

UUIDv7 advantages:
- Millisecond-precision timestamp prefix → sequential inserts, no B-tree splits
- Random suffix → collision-free across shards/regions
- No coordination between servers needed

ALTERNATIVES (use only when condition is met):
- Auto-increment integers: ONLY for small single-server apps needing short, human-readable IDs
- UUIDv4: ONLY for opaque tokens (API keys, session IDs) where time-ordering is undesirable
- CUID2: Acceptable alternative to UUIDv7 for Prisma users (Prisma 7 defaults to cuid())
- NanoID: Acceptable for short, URL-safe identifiers where collisions are tolerable

Prisma 7 (pure TypeScript):

model User {
  id        String   @id @default(cuid())  // CUID2, time-ordered
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}