database-design

Start with third normal form by separating entities into their own tables and linking them with foreign keys. For example, split customers, products, and orders into separate tables rather than duplicating customer data on every order row.

Use SQL databases like PostgreSQL when you need complex joins, ACID transactions, and structured data. Choose NoSQL document stores for flexible schemas, key-value stores like Redis for caching, and time-series databases for metrics and logs.

Replace per-record queries with a single JOIN query that fetches parent and child records together. With Prisma, use the include option to eager-load related records in one query instead of looping through results.

Index columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements, especially those with high cardinality. Avoid indexing small tables, low-cardinality columns like booleans, and columns already covered by composite indexes.

Use multi-step migrations: add the new column, backfill data, then swap columns in a separate deployment. In PostgreSQL, create indexes with CREATE INDEX CONCURRENTLY to avoid locking the table during the build.

Denormalize only after normalizing first and measuring actual performance bottlenecks. Common cases include caching computed aggregates or snapshotting historical values like price_at_purchase on order items to preserve accuracy.