Database Schema Design Skill

Choose the right data model for the engine — don't force one paradigm onto the other

MongoDB (document): model around access patterns, not normalization. Ask "what do I read together most often?" before "what is technically non-redundant?"

• Embed when data is always read/written together, has a bounded size, and doesn't need independent querying (e.g. order.shippingAddress, post.comments if comment volume is small and bounded).
• Reference (separate collection + ObjectId) when data is large/unbounded (thousands of comments), shared across many parents, or needs independent access/updates (e.g. user, product).
• Avoid unbounded array growth inside a single document (MongoDB's 16MB doc limit) — if a collection can grow indefinitely (e.g. all-time order history on a user doc), reference it instead.

Postgres/relational (Prisma/Drizzle): normalize by default (3NF), denormalize deliberately only for measured read-performance needs (e.g. a materialized view or a cached count column), and document why when you do.

Indexing rules

• Index every foreign key / reference field used in WHERE/$match queries.
• Compound indexes: order fields by (equality filters first, then range/sort fields) — e.g. { orgId: 1, createdAt: -1 } for "latest items in this org."
• Don't over-index — every index costs write performance and storage; add indexes based on actual query patterns, not speculatively.
• Use explain()/EXPLAIN ANALYZE to verify a slow query is actually hitting the index you expect before declaring it optimized.