Database Design

Choose SQL for structured data with complex queries and ACID transactions; choose NoSQL document stores for flexible schemas and rapid iteration. Key-value stores fit caching and sessions, while graph databases suit relationship-heavy data like social networks.

Run EXPLAIN ANALYZE to read the execution plan, then add indexes on WHERE and JOIN columns. Use composite indexes with correct column order, partial indexes for filtered subsets, and covering indexes that include all queried columns.

Prisma provides a declarative schema file with type-safe generated clients and relation includes. Drizzle defines schemas directly in TypeScript with a SQL-like query API, giving closer control over generated SQL.

Denormalize for read-heavy workloads, queries joining many tables, reporting tables, or when eventual consistency is acceptable. Keep the normalized schema as the source of truth and maintain denormalized summaries for performance.

Add new columns as nullable first, backfill data in batches, update the application to write both columns, then enforce constraints and drop old columns last. Never modify existing migrations; use small forward-only incremental changes.

Embed for 1:1 or 1:few relationships accessed together with bounded size, such as order items. Reference for 1:many or many:many relationships, independently accessed data, or unbounded growth like user posts.