db-schema

Overview

Enforce queries-first schema discipline — design every table around its access patterns, index every filter, and verify migrations are safe at production scale.

Database Schema Design

Persistence

ACTIVE on every schema change. Every CREATE TABLE, every ALTER, every migration. Think at scale before writing DDL.

The Discipline

Before writing ANY schema:

1. QUERIES FIRST — what queries will run against this? (schema serves queries, not the other way around)
2. SCALE QUESTION — what happens at 10M rows? 100M? Does this still work?
3. INDEX PLAN — every WHERE, JOIN, ORDER BY gets an index. No exceptions.
4. MIGRATION SAFETY — can this ALTER run without locking the table for minutes?
5. VERIFY — explain analyze the critical queries. Prove they use indexes.

Rules (never break)

• Never CREATE TABLE without knowing the top 5 queries it serves
• Never deploy a migration without testing against production-sized data
• Never use FLOAT for money (DECIMAL or integer cents)
• Never skip explicit created_at/updated_at on any table
• Never add a column with DEFAULT on a large table (locks it)
• Never DROP COLUMN in one step (stop writing → deploy → then drop)

Before creating any table:

• Define the access patterns FIRST (queries drive schema, not the other way around)
• Choose normalization level based on read/write ratio
• Plan for the 10x scale (what happens at 10M rows?)
• Identify foreign keys and cascade behavior
• Define indexes for every WHERE/JOIN/ORDER BY clause
• Plan soft-delete vs hard-delete strategy
• Consider multi-tenancy needs upfront

Normalization Decision

Situation	Strategy	Why
Read-heavy, rarely changes	Denormalize	Avoid JOINs at query time
Write-heavy, consistency critical	Normalize (3NF)	Single source of truth
Analytics/reporting	Star schema (denormalized)	Optimized for aggregation
User-facing CRUD	3NF with strategic denorm	Balance of integrity + speed