Database Design for Web Apps: The 9 Rules I Wish I Knew Earlier

Databases are where web apps go to die. Bad schema decisions in week 1 become $50,000 migration projects in year 3. Here are the nine rules I follow on every new project.

Rule 1: Normalize first, denormalize later (with evidence)

Start in 3rd normal form. Denormalize only when you have a measured query problem — not because someone said "joins are slow." Modern Postgres and MySQL handle joins fine.

Rule 2: Use UUIDs as public IDs, integers as primary keys

Auto-increment integers for performance and clustering. Public-facing UUIDs (or ULIDs) for URLs and APIs. Never expose internal IDs — they leak data ("User #5 just signed up means we have 4 users").

Rule 3: Index every foreign key, every where-clause column, and every order-by column

The default. Missing indexes is the #1 cause of slow apps. Use EXPLAIN to verify.

Rule 4: Soft delete sparingly

Soft deletes (deleted_at column) are convenient but pollute every query. Use them only for compliance/audit needs, not as a default. Real deletes are simpler.

Rule 5: Migrations are code

Every schema change goes through a migration file, version-controlled, reversible. No manual ALTER TABLE in production. Ever.

Rule 6: Use the right type

VARCHAR(255) for everything is lazy. ENUM for fixed sets, JSONB for flexible structures, TEXT for unbounded content, DECIMAL for money (never FLOAT).

Rule 7: Constraints belong in the database

NOT NULL, UNIQUE, CHECK, FOREIGN KEY. The application is not the only thing writing to your database — backups, scripts, future devs all bypass app-level validation. The database is your last line of defense.

Rule 8: Plan for 100x your current scale

Schema decisions made for 1,000 users break at 100,000. Indexes, partitioning, read replicas — think about them on day 1, even if you implement on day 365.

Rule 9: Backups are not real until you restore them

Automated daily backups + quarterly restore drill. A backup you have never restored is a hope, not a plan.

Need a database review? I will audit your schema and queries in a 1-week engagement and deliver a prioritized fix list.