Databases
Relational model: multiple-choice review
Crux Multiple-choice synthesis across the relational-model unit: keys, normal forms, JSONB vs side tables, FK at scale, TOAST storage, and zero-downtime schema change.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. None is a definition to recite — each is a schema-design decision you make under real constraints, where the wrong call is paid back at migration time, not design time.
Goal
Confirm you can connect the unit’s spine: keys and constraints, normal forms vs deliberate denormalization, JSONB vs typed columns vs side tables, FK economics at scale, physical storage, and how a schema changes without downtime.
Quiz
Completed
A customer table uses email as its PRIMARY KEY. Marketing now wants users to be able to change their email. What is the core problem, and the standard fix?
Heads-up Index width is not the issue; a TEXT email indexes fine. The problem is mutability: a changing PK must cascade through every referencing FK. A hash is still derived from the mutable email.
Heads-up Postgres only cascades a PK change if every FK is declared ON UPDATE CASCADE, and even then the rewrite touches every child row under lock. The durable answer is to never put a mutable value in the PK.
Heads-up A duplicate uniqueness guarantee does not remove the mutability problem — email is still the PK, so changing it still cascades. You must demote email out of the PK role entirely.
Quiz
Completed
A table stores `orders(order_id, customer_id, customer_city)` where `order_id` alone is the primary key. Which normal form does it violate, and what breaks in production?
Heads-up 2NF partial dependency only applies to composite primary keys. Here order_id alone is the PK, so there is no partial-key dependency to violate.
Heads-up A city name is a single atomic value. 1NF is about repeating groups and multi-valued cells, not transitive dependencies.
Heads-up It is only valid denormalization if the duplicate is reconciled by something (trigger, job) AND the intent is a point-in-time snapshot. Here it is an unreconciled transitive dependency, which is exactly the 3NF failure mode.
Quiz
Completed
A marketplace needs free-form product tags supporting four queries: show a product's tags, list all products with tag X, count products per tag, and rename a tag globally. Which storage shape satisfies all four cheaply at scale?
Heads-up The array handles 'tags on this product' and (via GIN) 'products with tag X', but aggregating per tag is expensive and a global rename means rewriting every row that contains the tag. It fails two of the four queries at scale.
Heads-up JSONB is strictly worse than the array here — higher parse overhead, no uniqueness or FK to a canonical tag list — and still cannot cheaply count-per-tag or rename globally.
Heads-up That duplicates the tag fact across orders with no reconciliation, recreating the 3NF disagreement problem, and still does not give you a count-per-tag or single-row rename.
Quiz
Completed
A senior engineer proposes disabling foreign keys across a typical B2B SaaS schema (tables under 100M rows) to 'improve write performance'. How should you read this proposal?
Heads-up The FK check is ~5-50 μs per row, negligible for a sub-100M-row SaaS. 'Hyperscale shops disable FKs' is a scale-specific exception, not a general best practice.
Heads-up Application-level enforcement is implemented inconsistently and breaks during refactors and across services. The FK is the single boundary every caller gets for free.
Heads-up An FK-column index helps query performance, not FK-check cost, and it does not address the real question of whether disabling the constraint is warranted at this scale. It is not.
Quiz
Completed
`SELECT * FROM events ORDER BY created_at DESC LIMIT 100` on a 50M-row table with an index on created_at is 3× slower than expected. The table has an 8KB-average JSONB payload column. Most likely cause and fix?
Heads-up A B-tree on created_at supports a reverse index scan for ORDER BY ... DESC LIMIT 100; the scan is cheap. The cost is the per-row TOAST fetch from SELECT *.
Heads-up A selective index returning 100 rows uses an index scan on any table size. Selectivity, not row count, drives the planner's choice here.
Heads-up JSONB can be returned in any query. There is no special slow path; the extra cost is fetching the TOASTed payload for each row.
Quiz
Completed
A `user_name` column must become `display_name` on a 50M-row table shared by many services, with zero downtime. Which approach is correct, and why is the one-line rename wrong?
Heads-up The rename is metadata-only and instant, but it atomically breaks every consumer still querying user_name. On a shared schema with many services that cannot deploy simultaneously, that is an immediate outage.
Heads-up A view eases reads, but writes through it need INSTEAD OF triggers or updatable-view rules, and it is only a transitional bridge — not a substitute for the full expand-then-contract migration.
Heads-up Downtime may be acceptable for an internal tool, but the prompt requires zero downtime, and the window still forces all consumers to deploy updated code simultaneously. Expand-then-contract is the zero-downtime answer.
Recap
The through-line across the unit is one decision discipline: put identity in an immutable surrogate key and guard business keys with UNIQUE NOT NULL; normalize to 3NF so no two rows can disagree, and denormalize only with explicit reconciliation; reach for a side table the moment you query from the other direction; keep FK constraints unless a measured sharding or cascade constraint forces otherwise; remember that physical storage (TOAST, page layout) makes SELECT * expensive on wide rows; and change a live schema with expand-then-contract, never an in-place break. Every answer here resolves back to “design for the queries and the lifetime, not the first insert.”