Relational Schema Design and Normalization Questions

Designing schemas for relational databases and applying normalization principles to reduce redundancy and maintain data integrity. Candidates should understand the normal forms including first normal form, second normal form, third normal form, and Boyce Codd normal form; primary keys, foreign keys, referential integrity, and how to model relationships such as one to one, one to many, and many to many using junction tables. Coverage includes entity relationship modeling, data modeling techniques, handling hierarchical or recursive data, choosing appropriate data types, and recognizing normalization violations in poorly designed schemas. Also discuss practical denormalization trade offs for performance, when and how to intentionally denormalize, designing schemas for maintainability and common query patterns, and considerations for analytics schemas such as star schemas and slowly changing dimensions.

HardTechnical

32 practiced

An analytical query performs joins across a dozen normalized tables and runs for minutes. Propose a systematic approach to optimize it: profiling to find hotspots, possible denormalization strategies, adding indexes or materialized views, rewriting the query, or changing the execution engine. For each option provide decision criteria and expected gains.

Sample Answer

Start with measurement, then iterate. Outline:

1) Profile to find hotspots- What to do: capture query plan (EXPLAIN/EXPLAIN ANALYZE), runtime stats, CPU/IO waits, shuffle/ network metrics (Spark UI, Query History), and row counts at each join/filter.- Decision criteria: if most time spent in a specific join, scan or exchange step; high re-shuffling or skew; or repeated subqueries.- Expected gains: informs targeted fixes; prevents wasted effort. Often reveals a single cause that, when fixed, cuts runtime by 2x–10x.

2) Add/adjust indexes (OLTP or column-store)- What to do: create B-tree, hash, or bitmap indexes on join/filter keys; consider covering indexes for selected columns.- Decision criteria: high table scans on selective predicates, small update frequency, indexes fit memory, and optimizer chooses index scans.- Expected gains: reduces I/O; selective queries can go from minutes to seconds (5x–50x) for read-heavy, selective workloads.

3) Materialized views / pre-aggregations- What to do: precompute expensive joins or aggregations and refresh incrementally or on schedule (MV, summary tables).- Decision criteria: repeated same aggregation/joins, acceptable staleness, write cost manageable.- Expected gains: transforms minutes-long analytics into seconds; 10x–100x for repeated queries.

4) Denormalization- What to do: flatten frequently-joined dimensions into fact table or create wide tables/denormalized snapshots.- Decision criteria: read-heavy analytics, joins are the bottleneck, storage acceptable, and ETL complexity tolerable.- Expected gains: eliminates expensive joins and shuffles; often 5x–50x improvements, at cost of storage and update complexity.

5) Rewrite query / logical optimizations- What to do: push filters before joins, remove unnecessary columns, replace correlated subqueries with joins or MVs, use broadcast joins when one table is small, use hash vs sort-merge choice.- Decision criteria: optimizer plan shows late filtering, or small table repeatedly shuffled.- Expected gains: often 2x–10x with low risk.

6) Change execution engine / configuration- What to do: enable vectorized execution, increase parallelism, tune join strategy, or move workload from RDBMS to MPP/columnar store (e.g., Snowflake, Redshift, BigQuery) or Spark with better resources.- Decision criteria: inherent engine limitations (single-threaded stages), extreme scale, or need for distributed computing.- Expected gains: variable — configuration can yield 2x–5x; switching engines can yield 10x+ but has migration cost.

Operational checklist and trade-offs:- Measure baseline and run A/B tests; track latency, cost, freshness.- Prefer low-risk fixes first: profiling → rewrite → indexes → materialized views → denorm → engine change.- Consider data freshness, storage costs, and maintenance overhead for denorm/MV.- Rollback plan and monitoring for stale/incorrect results.

This systematic path prioritizes low-effort, high-impact fixes informed by profiling; only incur denormalization or engine migration when necessary.

HardTechnical

64 practiced

You must migrate an existing customers table (id, email, address) to support SCD Type 2 without downtime. Provide a step-by-step migration plan including schema changes, backfill strategy, how to keep OLTP writes working during migration, cutover steps, and rollback strategy. Address correctness and performance concerns.

Sample Answer

Requirements & constraints (clarify up front)- Preserve full history (SCD Type 2) for address/email changes.- Zero or near-zero downtime for OLTP writes/reads.- Must be correct, idempotent, performant at production scale.

High-level approach1) Add new SCD table (append-only), backfill historical rows, keep live writes in sync via dual-write/CDC, validate, then switch reads to SCD view. Rollback by switching consumers back to original table and stopping sync.

Step-by-step plan

1. Schema changes (non-destructive)- Create customers_scd: - scd_id (surrogate PK, bigint) - business_id (original id) - email, address, other attrs - effective_from TIMESTAMP NOT NULL - effective_to TIMESTAMP NULL - is_current BOOLEAN NOT NULL (or use effective_to IS NULL) - source_version / change_tx_id metadata- Add indexes: (business_id, is_current) and partition by effective_from or business_id hash for scale.- Keep original customers table unchanged.

2. Backfill strategy (historical data)- Batch job (Spark/DB-native), idempotent: - For each business_id, sort by last_updated (or infer event order), create SCD rows with effective windows. - Write in bulk to customers_scd using upsert/INSERT ignoring duplicates. Use staging table + transactional insert for atomic chunks.- Throttle parallelism to avoid DB overload. Partition by business_id ranges to parallelize safely.- Validate counts, checksums per business_id, and sample records.

3. Keep OLTP writes working during migration- Two-phase sync: a) Short term: Implement a write-side adapter in the application layer to dual-write to customers_scd on every update/create (best for correctness). b) If app changes risky, enable CDC streaming (Debezium / db-log) that captures changes from customers and applies to customers_scd in near real-time, implementing idempotent apply logic that updates current row (set effective_to, insert new row).- Ensure CDC consumer processes changes in order per business_id (use transactional offsets / partitioning).- Maintain read consistency for OLTP: continue reading from customers table until cutover; writes are mirrored.

4. Continuous validation while syncing- Run ongoing reconciliation jobs that compare current state between customers and customers_scd (is_current rows) for divergence, log mismatches, auto-repair if safe.- Monitor lag, processing errors, and metrics (throughput, latency).

5. Cutover steps (zero-downtime)- Put a brief read-fanout layer (feature flag) in the service/data access layer: - Start routing analytical / downstream consumers to read from a materialized view or query that selects is_current rows from customers_scd. - When reconciliation metrics show near-zero mismatches and CDC lag is minimal, flip flag to make customers_scd the primary read source for analytics and downstream.- For OLTP, if desired, after validation switch primary writes to customers_scd (application change) or keep writing to legacy table and maintain CDC for historical continuity. Prefer switching writes if clients accept a short maintenance window (<1 minute) for a config toggle.

6. Rollback strategy- If serious issue pre-cutover: stop CDC/write adapter, roll back app feature flag so reads stay on customers table; customers_scd can be fixed and resynced.- If post-cutover failure: switch reads back to legacy customers table immediately; replay or reconcile missing SCD events from CDC log to re-create SCD state before next try.- Keep original customers table intact until decommissioned.

Correctness concerns & mitigations- Ordering: ensure per-business_id ordering in CDC or dual-write; use change_tx_id/timestamps to order and dedupe.- Idempotency: CDC/apply logic must be idempotent (use unique change id or compare existing values before inserting).- Race conditions: when dual-writing, write both within a local transaction where possible; if not, make reconciliation tolerant.

Performance concerns & mitigations- Index and partition SCD table to support high write throughput and fast current-row reads.- Backfill during off-peak windows and throttle writes. Use bulk inserts via staging tables to reduce WAL pressure.- Monitor DB write/replica lag and scale horizontally (read replicas) or vertically as needed.- Use incremental backfill and reconcile in chunks to bound transaction sizes.

Post-migration cleanup- After stable operation and downstream adoption, drop legacy read paths and remove dual-write/CDC. Archive customers table schema and data retention policy.- Document migration steps, issues, and automated reconciliation scripts.

This plan balances correctness (ordering, idempotency, validation) with safety (non-destructive changes, gradual cutover) and performance (partitioning, throttled backfill, CDC).

MediumSystem Design

43 practiced

Design a partitioning strategy for a large transactions table with 1 billion rows that is used by both OLTP operations and nightly analytics. Discuss partition key choices (date, customer_id, range/hash), partition maintenance (retention, dropping old partitions), and the impacts on foreign keys and joins.

Sample Answer

Requirements & constraints:- Mixed OLTP (low-latency single-row reads/writes) and nightly analytics (large scans/aggregations).- 1B rows, frequent inserts, retention policy ~1–3 years, joins to customers/orders.

High-level approach:- Hybrid partitioning: RANGE on transaction_date as primary partitioning for efficient time-windowed analytics and retention; subpartition HASH on customer_id (or use partition pruning + distributed index) to avoid hotspotting for high-cardinality customers in OLTP.

Partition key choices:- transaction_date (RANGE): excellent for nightly analytics, easy to drop old data, enables partition pruning for recent-date queries.- customer_id (HASH subpartitioning or clustering): spreads write load, improves single-customer OLTP performance if subpartitioning supported.- Avoid only HASH on customer_id: hard to do time-based retention and expensive full-history scans.

Partition maintenance:- Create daily or weekly RANGE partitions depending on ingest volume (daily for high volume).- Automated partition creation via job or DB-managed interval partitioning.- Retention: maintain rolling window (e.g., 2 years). Drop/truncate partitions instead of DELETE for fast reclamation.- Backups: snapshot partition before drop if regulatory.- Reorg/merge: merge small partitions if low volume; split if a partition grows too large.

Impacts on foreign keys and joins:- Foreign keys: some DBs don’t support FK across partitions or performance degrades. Denormalization or application-level checks may be necessary. If FK required, keep referenced tables collocated (same partition key like customer_id) or use global indexes.- Joins: joining on transaction_date is rare; prefer joining on customer_id. Collocation (co-partitioning) of large fact and dimension tables by customer_id reduces shuffles in distributed query engines (Spark, distributed DB).- Analytics: ensure statistics and partition-level metadata are kept current for planner to prune.

Trade-offs:- RANGE + HASH balances retention and OLTP spread but adds complexity in maintenance and possible FK limitations.- Alternatively, keep OLTP table small (recent N days) and move historical data to an append-only analytics store (Parquet on Data Lake) for heavy analytics, simplifying OLTP scale.

This design prioritizes fast retention operations and analytics performance while mitigating OLTP hotspots; implement automation for partition lifecycle and monitor query patterns to iterate.

EasyTechnical

45 practiced

Design a simple audit_log table schema to record changes to critical user profile fields: user_id, changed_by, changed_at, field_name, old_value, new_value. Specify types for each column, indexes to support queries by user_id and time range, and retention/archival strategies for large volumes of audit data.

Sample Answer

Suggested schema (Postgres-compatible):

audit_log- id: BIGSERIAL PRIMARY KEY- user_id: UUID NOT NULL -- user whose profile changed- changed_by: UUID NOT NULL -- actor (user or service)- changed_at: TIMESTAMPTZ NOT NULL -- timestamp with timezone- field_name: VARCHAR(128) NOT NULL- old_value: TEXT -- use TEXT or JSONB depending on field types- new_value: TEXT- meta: JSONB DEFAULT '{}' -- optional: client_ip, request_id, reason

Indexes / physical design:- CREATE INDEX idx_audit_user_time ON audit_log(user_id, changed_at DESC); -- fast lookups per user + recent changes- CREATE INDEX idx_audit_time ON audit_log(changed_at DESC); -- range queries across time- If queries filter by field: CREATE INDEX idx_audit_field ON audit_log(field_name);- Consider BRIN index on changed_at for very large tables partitioned by date.

Partitioning & scaling:- Range-partition by month on changed_at (e.g., PARTITION FOR VALUES FROM ... TO ...) to prune queries and make deletion efficient.- For huge scale, ingest into a write-optimized store (e.g., Kafka → ClickHouse / BigQuery / Redshift Spectrum) and store raw events as compressed Parquet in S3/GCS.

Retention & archival strategy:- Hot tier: keep last 6–12 months in the DB for fast queries.- Cold tier: monthly partitions older than retention are exported to Parquet -> S3 (partitioned by year/month/user) and deleted from primary DB.- Policy: automated job (Airflow) to export then DROP partition. Use lifecycle rules to transition to Glacier/Archive after N months.- Ensure exports include schema versioning and are immutable; keep checksums and NAT logs for compliance.

Other considerations:- Make rows immutable (no updates) — use INSERT-only to preserve audit integrity.- Add write-ahead append-only log or signed events for tamper-evidence.- Encrypt at rest and in transit; apply RBAC for query access.- For analytics, build derived tables or materialized views (e.g., last_profile_state) updated via streaming jobs.

EasyTechnical

41 practiced

Given a single table: Orders(order_id, customer_name, customer_email, item_id, item_name, qty), identify the normalization violations and list the minimal set of tables you would split this into to reach 3NF. Explain the update, insertion, and deletion anomalies that the current design allows.

Unlock Full Question Bank

Get access to hundreds of Relational Schema Design and Normalization interview questions and detailed answers.

Join thousands of developers preparing for their dream job.