Relational Database Fundamentals and Design Questions

Core concepts of relational databases and schema design including tables, relationships such as one to one one to many and many to many, primary keys and foreign keys, data integrity constraints, and the properties of atomicity consistency isolation and durability and why they matter. Understand differences between relational systems using structured query language and nonrelational databases, indexing strategies, normalization and denormalization trade offs, simple query optimization techniques, and when to choose a normalized relational design versus a document or key value store. Candidates should be able to perform basic entity identification, produce simple schema diagrams, explain persistence and durability considerations, and reason about basic performance and scaling trade offs.

MediumTechnical

22 practiced

Write a SQL query using window functions to compute each user's average session length per calendar month and then return the top 5 users per month by average session length. Given table:

sql

sessions(user_id INT, session_id UUID, start_ts TIMESTAMP, end_ts TIMESTAMP)

Use PostgreSQL syntax and explain partitioning and ordering choices.

Sample Answer

Approach: compute session length, bucket by calendar month, aggregate average length per user per month, then use ROW_NUMBER() partitioned by month ordered by avg desc to pick top 5.

sql

WITH session_lengths AS (
  SELECT
    user_id,
    -- month bucket as first day of month for easy grouping
    date_trunc('month', start_ts) AS month_start,
    EXTRACT(EPOCH FROM (end_ts - start_ts)) AS session_seconds
  FROM sessions
  WHERE end_ts IS NOT NULL AND end_ts > start_ts
),
user_month_avg AS (
  SELECT
    user_id,
    month_start,
    AVG(session_seconds) AS avg_session_seconds
  FROM session_lengths
  GROUP BY user_id, month_start
),
ranked AS (
  SELECT
    user_id,
    month_start,
    avg_session_seconds,
    ROW_NUMBER() OVER (
      PARTITION BY month_start           -- rank within each calendar month
      ORDER BY avg_session_seconds DESC, user_id ASC
    ) AS rn
  FROM user_month_avg
)
SELECT
  month_start,
  user_id,
  avg_session_seconds
FROM ranked
WHERE rn <= 5
ORDER BY month_start DESC, avg_session_seconds DESC;

Explanation:- date_trunc('month', start_ts) groups sessions into calendar months.- EXTRACT(EPOCH FROM ...) yields session length in seconds.- AVG(...) computes per-user monthly average.- ROW_NUMBER() PARTITION BY month_start ranks users separately per month; ORDER BY avg_session_seconds DESC selects top durations; tie-breaker user_id ASC for deterministic ordering.Edge cases:- Sessions spanning months: this uses start_ts month; alternatively split sessions by month if required.- Null/negative durations filtered.- Consider COUNT(*) filter to require minimum sessions before ranking (e.g., HAVING COUNT(*) >= 3).

EasyTechnical

20 practiced

For a simple messaging application, explain when you would choose a relational database versus a document store or key-value store. Consider requirements like transactional guarantees, flexible schema, query complexity (searching messages by content, by user, by time), scaling, and cost.

Sample Answer

For a simple messaging app the choice depends on exact requirements. I’d weigh transactional guarantees, schema flexibility, query patterns, scale, and cost:

When to choose a relational DB (e.g., PostgreSQL)- Use if you need strong ACID transactions (e.g., money-like credits, message delivery confirmations, strict ordering).- Good when complex joins or multi-table consistency matter (users, groups, permissions).- Querying by user, time, and joins (e.g., unread count per user across channels) is straightforward with indexes.- Trade-off: vertical scaling limits and potentially higher operational cost at very large scale.

When to choose a document store (e.g., MongoDB)- Use if message objects are semi-structured (attachments, reactions) and schema evolves.- Efficient to store a conversation as a document or thread with embedded messages for fast read of recent history.- Supports indexed queries by user/time and text search (with text indexes or integrated search).- Offers easier horizontal scaling and flexible schema; weaker multi-document transactions (but modern DBs support multi-doc transactions with caveats).

When to choose a key-value store (e.g., Redis, DynamoDB simple table)- Use for ultra-low-latency access patterns: session data, presence, caching, delivering the latest message, or rate-limiting.- Best when access is by single key (conversationID → message list or last-read timestamp). Not ideal for complex content search.- Very cost-effective and highly scalable for simple patterns, but limited querying and weaker querying-by-content.

Examples:- Small startup: start with Postgres for simplicity and integrity. Move heavy read paths to Redis cache and use a document DB for attachments if schema diverges.- High-scale chat (millions of users): store canonical messages in a horizontally sharded document store or DynamoDB, use Elasticsearch for full-text search, Redis for presence and recent messages, and rely on eventual consistency for some counters.

In summary: choose relational for strict consistency/complex joins; document stores for flexible schema and easy horizontal scaling; key-value for low-latency, simple-key access. Often a hybrid approach (relational + KV cache + search index) gives the best trade-offs for cost and scalability.

EasyTechnical

21 practiced

Explain normalization: 1NF, 2NF, 3NF (and mention BCNF). For each normal form give a short example of a table that violates it and show the normalized schema. Explain the practical reasons for normalizing and situations where you might stop before BCNF in production.

Sample Answer

Normalization is a set of rules to reduce redundancy and update anomalies by organizing tables and dependencies.

1NF (First Normal Form)- Rule: Atomic (no repeating groups), each column holds one value.- Violating table: Orders(order_id, customer, items) where items = "pen,notebook"- Normalized: Orders(order_id, customer) OrderItems(order_id, item)- Why: Enables querying individual items, avoids parsing strings.

2NF (Second Normal Form)- Rule: In a table with a composite primary key, every non-key attribute must depend on the whole key (no partial dependency).- Violating table: OrderItems(order_id, product_id, product_name, quantity) PK=(order_id, product_id) but product_name depends only on product_id.- Normalized: OrderItems(order_id, product_id, quantity) Products(product_id, product_name)- Why: Prevents repeating product info per order.

3NF (Third Normal Form)- Rule: No transitive dependencies: non-key attributes must not depend on other non-key attributes.- Violating table: Employees(emp_id, dept_id, dept_name) dept_name depends on dept_id (non-key).- Normalized: Employees(emp_id, dept_id) Departments(dept_id, dept_name)- Why: Changes to department name happen in one place.

BCNF (Boyce-Codd NF)- Stronger than 3NF: every determinant must be a candidate key. Rare edge case where 3NF allows anomalies (e.g., a table with overlapping candidate keys). Normalize further into additional tables.

Practical reasons to normalize:- Eliminates update/insert/delete anomalies- Saves storage and improves consistency- Makes data semantics clearer

When to stop before BCNF:- Performance needs: denormalization (duplicating read-heavy attributes) to avoid expensive joins- Simplicity for analytics or caching layers- Controlled redundancy acceptable when it improves latency, with mechanisms to maintain consistency (triggers, application logic, or periodic jobs)

Summary: Normalize to 3NF for OLTP systems for correctness; consider targeted denormalization for performance in production, balancing consistency and speed.

HardTechnical

25 practiced

Explain how Multi-Version Concurrency Control (MVCC) works internally and how snapshot isolation differs from true serializability. Describe an example of a write-skew anomaly and how you would prevent it in a DBMS that provides snapshot isolation.

Sample Answer

MVCC internals (how it works)- Each row stores multiple versions (value + metadata): a creation timestamp (TXN ID) and a deletion/replace timestamp. On update/insert the DB creates a new version; old versions remain for readers.- Transactions get a consistent snapshot timestamp (or xmin/xmax). A read returns the newest version whose timestamps are visible to the reader’s snapshot (created before snapshot start and not deleted before snapshot start).- Writers normally operate by creating new versions and marking old ones obsolete; GC/vacuum later reclaims unreachable versions.- Concurrency control uses timestamps/transaction IDs and sometimes locks for writes; conflict detection can be done at commit.

Snapshot isolation vs true serializability- Snapshot Isolation (SI): each transaction reads from a snapshot taken at start; concurrent writers that update the same row typically cause a write-write conflict and one must abort. SI prevents many anomalies but does not guarantee that the outcome is equivalent to some serial order of transactions.- Serializability: every concurrent execution is equivalent to some serial execution. SI can admit anomalies where two transactions read overlapping data and write different rows, producing a final state impossible under any serial order.

Write-skew example- Two physicians A and B on-call. Table shifts(doctor, on_call BOOLEAN). Initially A.on_call = true, B.on_call = true. Business rule: at least one doctor must be on_call.- T1 (A): begin; reads A=true, B=true; sets A=false; commit.- T2 (B): begin (snapshot taken before T1 commit, so sees A=true,B=true); sets B=false; commit.- Both commit under SI because they wrote different rows (no write-write conflict). Result: A=false,B=false violates the invariant. No serial order yields this state, so it's a write-skew.

How to prevent write-skew under a DBMS with SI- Use Serializable Snapshot Isolation (SSI): detect dangerous rw-dependency cycles at runtime and abort one transaction to enforce serializability.- Acquire appropriate locks: issue SELECT ... FOR UPDATE (or FOR SHARE) on all rows that participate in the invariant so concurrent transactions conflict on write/lock and one blocks/aborts.- Enforce constraints at the DB level: use CHECK constraints, triggers, or materialized aggregated counters with atomic updates so invariant cannot be violated without conflicting writes.- Application-level: design transactions to update a canonical row (e.g., a single “on_call_count” row) so write-write conflict occurs.Choice rationale: SSI is safest and automated; explicit locking is simple and efficient when you can identify the relevant rows; constraints are robust but may be harder to express for complex predicates.

MediumSystem Design

22 practiced

Design a schema for user notifications where users can have zero or many notifications. The system should support marking notifications read/unread, fetching the latest N notifications, and fast unread counts. Explain table layout, indexes, and strategies to keep unread counters efficient at scale.

Sample Answer

Requirements:- Each user can have 0..N notifications- Support mark read/unread- Fetch latest N notifications (paged)- Fast unread counts at scale

Schema (relational example):- notifications - id (BIGSERIAL PK) - user_id (BIGINT, FK -> users) — partition key or sharding key - type (VARCHAR) - payload (JSONB) — flexible content - created_at (TIMESTAMP WITH TIME ZONE, indexed DESC) - is_read (BOOLEAN) — durable read flag - read_at (TIMESTAMP NULL) - priority (SMALLINT) optional

Indexes:- PRIMARY KEY(id)- INDEX (user_id, created_at DESC) — supports latest N fetch- PARTIAL INDEX (user_id) WHERE is_read = false — supports fast unread-only scans- INDEX (user_id, is_read, created_at DESC) if partial not supported

Scaling strategies for unread counters:1. Denormalized counter column (user_unread_count) in users table: - Increment on insert of is_read=false, decrement on mark-read. - Use transactions to keep strong consistency where feasible. - Use optimistic concurrency (UPDATE ... WHERE count = ?) or DB atomic operations (UPDATE users SET unread = unread + 1).

2. Eventual-consistent cache (Redis): - Maintain an unread counter in Redis per user (INCR/DECR). Serve reads from cache. - On write, update DB then update Redis; if Redis miss or failure, rebuild from DB. - Periodic reconciliation job to correct drift (batch COUNT(...) WHERE is_read=false and sync).

3. Sharded counter for hot users: - Maintain N shards per user (user_unread:{user_id}:{shard_id}) to avoid single-key contention; sum shards when reading.

Read/Write patterns and trade-offs:- For low latency reads, serve latest N from an indexed DB query, cache first page in Redis.- For strong consistency of unread counts, prefer DB-backed counter with transactional updates; for scale and throughput, prefer Redis with reconciliation.- If notifications are many, consider time-partitioned tables or per-user partitions and background TTL/archive for old notifications.

Edge cases:- Concurrent mark-read of same notification — use idempotent updates (set is_read=true, read_at=COALESCE(read_at, now())).- Lost cache updates — reconciliation job and on-cache-miss rebuild.

This design balances simple schema with fast queries and scalable counters using cache + reconciliation or sharded counters for very high write volume.

Unlock Full Question Bank

Get access to hundreds of Relational Database Fundamentals and Design interview questions and detailed answers.

Join thousands of developers preparing for their dream job.