Schema Design Template for Engineering Teams

UUIDs are better for distributed systems, API exposure (no enumeration attacks), and multi-region deployments. Auto-incrementing integers are smaller (8 bytes vs 16), produce more compact indexes, and are easier to debug by eye. For most SaaS products, UUIDs are the safer default because they decouple ID generation from the database and work well with microservices. Use UUID v7 (time-ordered) if your database supports it, as it preserves B-tree locality.

Denormalize when a frequently executed query requires joining 3 or more tables and the join is measurably slow, when you need to serve read-heavy API endpoints with sub-10ms response times, or when reporting queries on normalized data take too long. Common denormalization patterns: store a computed count on the parent row, duplicate a lookup value from a related table, or create a materialized view. Always document what is denormalized and why. The [data model template](/templates/data-model-template) covers the trade-offs in more detail.

The expand-contract pattern: (1) Expand: add new columns as nullable with defaults, deploy. (2) Migrate: backfill data, update application to write to both old and new columns. (3) Contract: remove old columns after all readers are updated. For table renames, create the new table, dual-write, backfill, switch reads, drop old table. Never rename a column in a single migration on a live system.

There is no fixed number, but each index slows writes (INSERT, UPDATE, DELETE) and consumes storage. Start with indexes only for your top 10 queries and primary/foreign keys. Monitor slow queries in production and add indexes reactively. Remove unused indexes quarterly using your database's index usage statistics (e.g. `pg_stat_user_indexes` in PostgreSQL). A table with 15 unused indexes is a common problem.

Use JSON columns (JSONB in PostgreSQL) for data that is: stored and retrieved as a whole unit, not filtered or sorted by individual keys, schema-flexible (different records have different keys), and low-volume. Create separate tables for data that is: queried by individual fields, joined with other tables, subject to uniqueness or foreign key constraints, or high-volume with consistent structure. The [data model template](/templates/data-model-template) covers this decision in the context of customizable fields. ---