database-schema

Use the expand/contract migration pattern to add non-nullable columns safely. This multi-step approach involves deploying backward-compatible schema changes first, backfilling data, and then enforcing constraints, ensuring zero-downtime database migrations in production.

Define one-to-many and many-to-many relations in Prisma using relation scalar fields and explicit relation tables. This approach maintains relational integrity, enforces data consistency constraints at the database level, and prevents common data modeling anti-patterns.

The expand/contract pattern works by splitting migrations into two phases: expanding the schema to add new columns while maintaining backward compatibility, then contracting to remove old fields after application code updates. This enables safe database schema evolution.

N+1 queries occur when applications execute individual queries for related records. Optimize schema performance by implementing strategic indexing, adding appropriate constraints, and structuring relations to support efficient batch retrieval in PostgreSQL.

Yes, this guidance specifically targets production applications using Prisma and PostgreSQL. It covers schema design, relational integrity, safe migration strategies, and data consistency patterns required for maintaining robust databases at scale.

Common schema design anti-patterns include improper normalization, missing indexes, and unsafe migrations that cause downtime. These limitations lead to data inconsistency, performance bottlenecks, and broken relational integrity in production environments.