Database Design Interview Questions: Schema, Indexing, and Transactions

Database design questions appear in nearly every senior engineering interview. You need to go beyond SELECT statements and demonstrate understanding of schema design, indexing strategy, transactions, and when to use SQL vs NoSQL.

Schema Design and Normalization

"""
Normal Forms:
  1NF: atomic columns, no repeating groups
  2NF: 1NF + no partial dependencies on composite key
  3NF: 2NF + no transitive dependencies (non-key → non-key)
  BCNF: every determinant is a candidate key (stronger than 3NF)

Denormalization: trade storage for query performance.
  Common in read-heavy systems, data warehouses, OLAP.
  Example: store user_name in orders table (denormalized) to avoid
  a JOIN on every order query.
"""

# Example: E-commerce schema (normalized to 3NF)
SCHEMA_ECOMMERCE = """
CREATE TABLE users (
    user_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      VARCHAR(255) UNIQUE NOT NULL,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE products (
    product_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku         VARCHAR(100) UNIQUE NOT NULL,
    name        VARCHAR(500) NOT NULL,
    price_cents INTEGER NOT NULL,
    category_id INTEGER REFERENCES categories(category_id),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE orders (
    order_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(user_id),
    status      VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_cents INTEGER NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    INDEX idx_orders_user_id (user_id),
    INDEX idx_orders_created_at (created_at)
);

CREATE TABLE order_items (
    order_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id      UUID NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id    UUID NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,  -- Snapshot price at order time
    UNIQUE (order_id, product_id)
);
"""

Indexing Strategy

"""
Index types and when to use them:

B-Tree index (default):
  - Range queries: WHERE created_at BETWEEN x AND y
  - Equality: WHERE email = ?
  - Sorting: ORDER BY last_name, first_name
  - Prefix match: WHERE name LIKE 'prefix%'
  - Cannot use LIKE '%suffix' (index can't seek from middle)

Hash index:
  - Equality only: WHERE id = ?
  - Faster than B-tree for equality; useless for range

GIN index (PostgreSQL):
  - Array containment: WHERE tags @> ARRAY['python']
  - Full-text search: WHERE to_tsvector(body) @@ query
  - JSONB key existence: WHERE data ? 'key'

Partial index:
  - Index only rows matching a condition
  - CREATE INDEX idx_active_users ON users(email) WHERE active = true
  - Much smaller than full index; hits cache better

Composite index column order:
  - Most selective column first (reduces scan early)
  - Equality columns before range columns
  - Match query predicates: (status, created_at) for WHERE status=? ORDER BY created_at
"""

INDEX_EXAMPLES = """
-- Covering index: index contains all columns the query needs (no heap fetch)
CREATE INDEX idx_covering ON orders(user_id, status, created_at, total_cents);
-- SELECT total_cents FROM orders WHERE user_id=? AND status=?
-- → Served entirely from index (no table access)

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));
-- WHERE LOWER(email) = LOWER(?) -- Uses index

-- Partial index for soft deletes
CREATE INDEX idx_active_posts ON posts(author_id, created_at)
WHERE deleted_at IS NULL;
"""

SLOW_QUERY_PATTERNS = """
-- COMMON ANTI-PATTERNS that prevent index use:

-- 1. Function on indexed column
WHERE YEAR(created_at) = 2024           -- Can't use index on created_at
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01'         -- This works

-- 2. Implicit type conversion
WHERE user_id = 12345                   -- user_id is VARCHAR: converts all rows!
WHERE user_id = '12345'                 -- Correct

-- 3. OR across columns (use UNION instead)
WHERE (status = 'pending') OR (priority = 'high')  -- Full scan
-- Better: two indexed queries merged with UNION ALL

-- 4. Leading wildcard
WHERE name LIKE '%tech%'               -- Full table scan
-- Use GIN/fulltext index instead

-- 5. SELECT * with JOIN on large tables — specify needed columns
"""

ACID Transactions and Isolation Levels

"""
ACID Properties:
  Atomicity:   All or nothing. Partial updates never committed.
  Consistency: Transaction moves DB from one valid state to another.
  Isolation:   Concurrent transactions behave as if sequential.
  Durability:  Committed transactions survive crashes (WAL/redo log).

Isolation Levels (weakest to strongest):
  Read Uncommitted: Dirty reads possible. Never use in practice.
  Read Committed:   No dirty reads. Default in PostgreSQL, Oracle.
                    Phantom/non-repeatable reads possible.
  Repeatable Read:  No dirty or non-repeatable reads.
                    MySQL InnoDB default. Phantoms possible.
  Serializable:     Full isolation. No anomalies. Highest contention.

Anomalies:
  Dirty read:            Read another transaction's uncommitted data
  Non-repeatable read:   Re-read same row, get different value
  Phantom read:          Re-run query, get different set of rows
  Lost update:           Two transactions overwrite each other
  Write skew:            Two transactions read same data, both decide
                         it's safe to update, violating a constraint
"""

import psycopg2

def transfer_funds(conn, from_account: int, to_account: int, amount: int):
    """Deadlock-safe fund transfer — acquire locks in consistent order."""
    first, second = sorted([from_account, to_account])

    with conn.cursor() as cur:
        conn.autocommit = False
        try:
            # Lock rows in ID order to prevent deadlock
            cur.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (first,))
            cur.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (second,))

            cur.execute("SELECT balance FROM accounts WHERE id = %s", (from_account,))
            balance = cur.fetchone()[0]

            if balance < amount:
                conn.rollback()
                raise ValueError("Insufficient funds")

            cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                        (amount, from_account))
            cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                        (amount, to_account))
            conn.commit()
        except Exception:
            conn.rollback()
            raise

def optimistic_locking_update(conn, product_id: int, new_price: int):
    """Optimistic locking via version column — no row lock held during read."""
    with conn.cursor() as cur:
        cur.execute("SELECT version FROM products WHERE id = %s", (product_id,))
        row = cur.fetchone()
        if not row:
            raise ValueError("Product not found")
        version = row[0]

        # ... business logic that takes time (no lock held) ...

        cur.execute("""
            UPDATE products SET price = %s, version = version + 1
            WHERE id = %s AND version = %s
        """, (new_price, product_id, version))

        if cur.rowcount == 0:
            raise Exception("Concurrent modification detected — retry")
        conn.commit()

Sharding Strategies

"""
Sharding: horizontal partitioning — data split across multiple nodes.

Key-based (hash) sharding:
  shard = hash(user_id) % num_shards
  Pro: even distribution
  Con: rebalancing when adding shards requires remapping

Range sharding:
  shard 0: user_ids 0–1M, shard 1: 1M–2M, ...
  Pro: range queries stay on one shard
  Con: hotspots (new users all go to latest shard)

Directory sharding:
  Lookup table maps key → shard
  Pro: flexible, can move data
  Con: lookup table is bottleneck/SPOF

Consistent hashing:
  Nodes on a ring; data maps to nearest node clockwise
  Adding/removing node affects only adjacent range (~1/N data moves)
  Pro: minimal rebalancing
  Con: uneven distribution without virtual nodes
"""

import hashlib

class ConsistentHashRing:
    def __init__(self, nodes: list, virtual_nodes: int = 150):
        self.ring = {}
        self.sorted_keys = []
        self.virtual_nodes = virtual_nodes
        for node in nodes:
            self.add_node(node)

    def _hash(self, key: str) -> int:
        return int(hashlib.md5(key.encode()).hexdigest(), 16)

    def add_node(self, node: str):
        for i in range(self.virtual_nodes):
            vnode_key = f"{node}:vn{i}"
            h = self._hash(vnode_key)
            self.ring[h] = node
            self.sorted_keys.append(h)
        self.sorted_keys.sort()

    def remove_node(self, node: str):
        for i in range(self.virtual_nodes):
            h = self._hash(f"{node}:vn{i}")
            del self.ring[h]
            self.sorted_keys.remove(h)

    def get_node(self, key: str) -> str:
        if not self.ring:
            raise Exception("No nodes in ring")
        h = self._hash(key)
        for ring_key in self.sorted_keys:
            if h <= ring_key:
                return self.ring[ring_key]
        return self.ring[self.sorted_keys[0]]  # Wrap around

CAP Theorem and Database Selection

Database	Type	CAP	Best For
PostgreSQL	Relational	CP	Transactions, complex queries, strong consistency
MySQL	Relational	CP	OLTP, web apps, wide ecosystem
Cassandra	Wide-column	AP	Time-series, IoT, high write throughput
DynamoDB	Key-value/document	AP	Serverless, predictable latency at any scale
MongoDB	Document	CP	Flexible schema, nested data, rapid iteration
Redis	In-memory KV	CP	Cache, sessions, leaderboards, pub/sub
Elasticsearch	Search	AP	Full-text search, log analytics
Neo4j	Graph	CA	Relationship traversal, recommendations
ClickHouse	Columnar	CP	OLAP, analytics, aggregations over billions of rows

Companies That Ask This System Design Question

This problem type commonly appears in interviews at:

• Databricks Interview Guide
• Snowflake Interview Guide
• Palantir Interview Guide

See our company interview guides for full interview process, compensation, and preparation tips.

Frequently Asked Questions

What are the four ACID properties of database transactions?

Atomicity: all operations in a transaction succeed or all are rolled back. Consistency: a transaction moves the database from one valid state to another. Isolation: concurrent transactions behave as if they ran serially. Durability: committed transactions survive crashes (persisted to disk via write-ahead log).

What is the difference between a clustered and non-clustered index?

A clustered index determines the physical order of rows on disk — there can only be one per table (typically the primary key). Non-clustered indexes are separate structures that contain indexed columns plus a pointer to the actual row. Reading from a non-clustered index on a non-covering query requires an extra lookup (the key lookup).

When would you use a NoSQL database over SQL?

Choose NoSQL when: you need horizontal sharding beyond what SQL can handle, your schema is flexible or evolving rapidly (document DB), you have high-throughput time-series data (Cassandra), you need sub-millisecond key lookups (Redis/DynamoDB), or you are modeling highly connected data (graph DB). SQL is better for complex queries, transactions, and when data consistency is critical.