Skip to content
Tech Interview Prep
Technical interview guide

Schema Design & Normalization

Structuring tables to avoid redundant, inconsistent data, and knowing when to deliberately break the rules for performance.

Read
24 min
Practice MCQs
25
Interview Q&A
25
Edition
v3
Editorial status
Reviewed

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Curated: · Written: · Reviewed:

25 questions

MCQ-1A schema review applies one rule: a column is atomic only if no requirement ever filters, constrains or updates part of its value on its own. The product must filter articles by a single tag and rename a tag across all articles; addresses and names are only ever read and written whole. Which column violates first normal form?
MCQ-2A table `order_lines` is keyed on `(order_id, line_no)` (one row per line, and each order belongs to a single customer) and stores `customer_id`, `customer_email`, `product_id`, and `qty`. A customer changes their email; the team updates 40 rows across 12 orders and misses 3, so the same customer now shows two different email addresses. Which is the lowest normal form this schema fails?
MCQ-15An `orders` table stores the customer's email on every row: `orders(order_id PK, customer_id, customer_email, total_cents)`. Customer 42 has 37 orders totalling $4,180.00; the most recent order is $120.00. When a customer changes their email, the app runs `UPDATE orders SET customer_email = ? WHERE order_id = ?`, passing the id of the order just placed. A monthly report runs `SELECT customer_email, SUM(total_cents) FROM orders GROUP BY customer_email`. Which statement explains the report's output for customer 42 after the email change?
MCQ-3On 1 March a product's list price rises from $19 to $24. Orders placed in February must still show $19 per line. Which reason justifies storing unit_price on order_line instead of joining product at read time?
MCQ-21A payments table has 48 columns. Counting non-NULL columns per row: 612,340 rows have only the card columns (card_last4, card_expiry) filled, 288,110 only the transfer columns (iban, bic), 96,220 only the wallet columns (wallet_token), 3,330 only the voucher columns (voucher_code), and 0 rows have two groups filled. What does this pattern indicate?
MCQ-17A `payments` table declares `status text NOT NULL CHECK (status IN ('pending','settled','refunded'))`. Finance needs a customer-facing label and a display order per status, the list gains a value about once a quarter, and the `refunds` and `disputes` tables must reference the same statuses. Which change should the team make?
MCQ-22A payments service stores each account's current risk tier in one column: `UPDATE accounts SET risk_tier = $1 WHERE id = $2`. The tier is not derived from any other retained data. On 2025-06-02 a regulator asks what account 8842's risk tier was on 2024-03-15. The service runs PostgreSQL 16 with nightly full backups retained 35 days, archived WAL retained 7 days, and application logs retained 30 days; the row's `updated_at` shows the tier last changed on 2025-01-09. What can the team produce for 2024-03-15?
MCQ-18A nightly load maintains `dim_customer` for a warehouse. `fct_orders` stores one integer `customer_key` per order, the source's natural key is a string `customer_ref`, and analysts must join each order to the customer version in effect on the order date using the predicate `order_date BETWEEN valid_from AND valid_to`. Which column set on `dim_customer` supports this?
MCQ-9A `subscriptions` table is keyed by `plan_code CHAR(8)`, the natural business identifier, and 11 child tables reference it with `ON UPDATE CASCADE`. Finance re-codes plans about 40 times a year; last quarter one re-code rewrote 6.2 million child rows and held locks for 19 minutes. The plan code must stay unique and queryable by name. Which change removes the cascade rewrite while keeping that rule?
MCQ-12`line_items` has `qty`, `unit_price_cents` and `total_cents`. Three services write rows and each computes `total_cents` itself; a fourth service added last quarter did not, and 4,000 rows now sit at 0. The team wants `total_cents` correct in the same transaction that writes the row, with no window where it is stale, and without any writer having to compute it. Which change gives them that?
MCQ-20A team must guarantee that no row in `ledger` has `amount_cents = 0`. The Go service that owns the table checks this before every insert, but a nightly Python reconciliation job and an ops psql console also write rows. Which argument settles where the rule belongs?
MCQ-4A PostgreSQL 16 session with autocommit on runs this script top to bottom: ```sql CREATE TABLE t (id int PRIMARY KEY, code text UNIQUE); INSERT INTO t VALUES (1, NULL); INSERT INTO t VALUES (2, NULL); INSERT INTO t VALUES (3, 'A'); INSERT INTO t VALUES (4, 'A'); ``` Which statement describes the table's contents once the script finishes?
MCQ-7PostgreSQL 16. Table `user_sessions` has `user_id bigint`, `session_id bigint primary key`, `active boolean not null`, `started_at timestamptz`. It currently holds: | user_id | session_id | active | | --- | --- | --- | | 42 | 1001 | true | | 42 | 1002 | true | | 42 | 1003 | false | The team runs: ```sql CREATE UNIQUE INDEX CONCURRENTLY one_active_session ON user_sessions (user_id) WHERE active; ``` What is the result, and what must they do before the index can exist?
MCQ-8A `bookings` table on PostgreSQL 14 is defined with `EXCLUDE USING gist (room_id WITH =, during WITH &&)`, where `during` is a `tstzrange`. Two sessions run at READ COMMITTED: ``` A: BEGIN; A: INSERT INTO bookings VALUES (7, tstzrange('2024-03-01 09:00', '2024-03-01 10:00')); B: BEGIN; B: INSERT INTO bookings VALUES (7, tstzrange('2024-03-01 09:30', '2024-03-01 10:30')); ``` A's transaction is still open. What does session B observe?
MCQ-5A signup service runs `SELECT 1 FROM users WHERE email = ?` and inserts when it returns no row, both statements inside one transaction on the primary at READ COMMITTED. The `users.email` column has no unique index. A load test fires 100 concurrent signups for the same email and the table ends up with 4 rows. Which mechanism produced the duplicates?
MCQ-6In PostgreSQL, a foreign key from invoices.customer_id to customers.id is declared DEFERRABLE INITIALLY DEFERRED. The team needs a DELETE on customers to fail at that statement, not at COMMIT, even if a later statement in the same transaction would delete the invoices first. Which ON DELETE action enforces that?
MCQ-19An `orders` table carries a denormalised `customer_tier` copied from `customers.tier`. The order service reads `customers.tier` inside the same transaction that inserts the order, at READ COMMITTED. An `AFTER UPDATE OF tier` trigger on `customers` runs `UPDATE orders SET customer_tier = NEW.tier WHERE customer_id = NEW.id`, and it fires once per changed customer row. A nightly audit on the primary flags about 1,200 mismatched orders a night, and every flagged order was inserted within the same second as its customer's tier change. What is the cause?
MCQ-23A payments table stores `amount` as `double precision`. For order 551 the application wrote the original charge as the literal `0.3` and later wrote a partial refund as `0.3 - 0.1`, computed in code. A support query runs `SELECT count(*) FROM payments WHERE order_id = 551 AND amount = 0.2`. What does it return?
MCQ-10A write-heavy table's primary key was migrated from a bigint sequence to random UUIDv4. Insert p99 rose from 15 ms to 120 ms, and the page cache now shows the index's leaf pages spread across the whole file instead of a few hot pages. Which property of random UUIDv4 explains the regression?
MCQ-11A 40M-row Postgres `orders` table is queried by screens that filter, join, sort and aggregate on its fields. Which of these five fields belongs in a `jsonb` column rather than its own typed column?
MCQ-16`enrollment(student_id, course_id, enrolled_at)` is keyed on `(student_id, course_id)`. The hot query is a course roster: `SELECT student_id FROM enrollment WHERE course_id = $1 ORDER BY student_id LIMIT 50`, run about 2,000 times a minute against a 300M-row table where a popular course holds ~30,000 enrolments. Which index should be added?
MCQ-25A PostgreSQL 16 events table holds 300M rows in 420 GB and takes 6,000 INSERT/s, with p99 insert latency of 7 ms. A report query: SELECT tenant_id, count(*) FROM events WHERE tenant_id = $1 AND created_at >= now() - interval '30 days' GROUP BY tenant_id: takes 38 s. The team adds CREATE INDEX ON events (tenant_id, created_at) and benchmarks it on a staging copy. What is the most likely outcome?
MCQ-13A PostgreSQL 16 table holds 400 million rows and takes writes around the clock. The team has a deploy window of a few seconds, and existing queries must keep working unchanged. Which change meets both constraints?
MCQ-24On PostgreSQL 14, `users` holds 300M rows and `email text` is nullable; no row currently has a NULL email. A plain `ALTER TABLE users ALTER COLUMN email SET NOT NULL` would scan the table under ACCESS EXCLUSIVE and block traffic for about four minutes, so the team runs three auto-committed statements: ```sql ALTER TABLE users ADD CONSTRAINT users_email_not_null CHECK (email IS NOT NULL) NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null; -- scans for ~90 s ALTER TABLE users ALTER COLUMN email SET NOT NULL; ``` What does the third statement do?
MCQ-14A service runs three instances behind a load balancer and deploys one instance at a time. To add `users.email_normalized`, a team plans one deploy in which v2 writes both `email` and `email_normalized` and reads `email_normalized`. Why must the read switch ship in a separate, later deploy?