data-modeling

Start by identifying entities, relationships, and access patterns, then normalize to 3NF and add foreign keys. Denormalize selectively only for read-heavy queries, and index all foreign keys and frequent filter columns.

Choose relational models for complex queries, joins, and ACID transactions. Choose document models like MongoDB for flexible schemas and horizontal scaling, graph models for relationship-heavy queries, and time-series tables for metrics.

Denormalize when read-heavy queries require expensive joins, such as embedding user names in orders. Accept the trade-off of slower writes and data redundancy, or use materialized views as an alternative.

Add new columns as nullable with defaults, backfill existing rows, then apply NOT NULL constraints in a separate step. Never add a NOT NULL column without a default to a populated table.

Composite indexes only cover queries filtering on the leftmost columns. An index on (user_id, status) covers queries on user_id alone but not on status alone, so column order must match query patterns.