Database Design And Access

Why this matters for a Backend Engineer

Reliable applications start with reliable data. Database Design and Access covers how you model entities, query efficiently, keep data consistent, and evolve schemas safely. Mastering this lets you ship features faster, handle growth, and avoid costly outages.

• Translate product requirements into relational models that are easy to query and change.
• Write SQL that is correct, predictable, and fast under load.
• Use transactions, isolation, indexing, connection pooling, and caching to meet performance and consistency goals.

Who this is for

• Backend engineers building APIs, services, or jobs that read/write relational databases.
• Developers moving from scripts to production-grade services.
• Anyone preparing for backend interviews or on-call duties.

Prerequisites

• Comfort with one backend language (e.g., Java, Go, Python, Node.js) and basic I/O.
• Basic command-line skills.
• Familiarity with fundamental SQL SELECT/INSERT/UPDATE/DELETE.

Learning path

1. Modeling: Entities, relationships, normalization, keys.
2. Querying: Joins, filtering, aggregations, pagination.
3. Correctness: Transactions, isolation, consistency patterns.
4. Performance & Ops: Indexes, pooling, migrations, caching.

Practical roadmap with milestones

1. Model core entities
   • Sketch ER diagrams for 3–5 core entities and their relationships.
   • Pick primary keys and choose when to use surrogate vs natural keys.
2. Write baseline queries
   • Implement common joins and aggregations used by your API endpoints.
   • Add safe pagination with keyset or cursor-based approaches for large datasets.
3. Enforce correctness
   • Wrap multi-step writes in transactions. Pick isolation that prevents your specific anomalies.
   • Add constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK) to shift errors left.
4. Speed it up
   • Create targeted indexes from actual WHERE/ORDER BY clauses.
   • Introduce a connection pool with sane limits. Add read-through cache for hot reads.
5. Evolve safely
   • Adopt a migration workflow (up/down or forwards-only).
   • Roll out schema changes in backward-compatible steps.

Worked examples (with code)

1) Relational modeling: posts and comments

-- Entities: users, posts, comments
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE comments (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL REFERENCES posts(id),
  user_id BIGINT NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT comment_per_post_body CHECK (length(body) > 0)
);

Rationale: Foreign keys maintain referential integrity; CHECK prevents empty comments. Add indexes where queries need them (e.g., comments by post).

2) Query with join and aggregation

-- Top 5 posts by comment count in the last 7 days
SELECT p.id, p.title, COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id AND c.created_at >= now() - interval '7 days'
GROUP BY p.id
ORDER BY comment_count DESC
LIMIT 5;

-- Helpful index:
-- CREATE INDEX idx_comments_post_recent ON comments(post_id, created_at);

3) Safe money transfer with transactions

BEGIN;
  -- lock both rows in a consistent order to reduce deadlocks
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;

  -- ensure invariant
  DO $$ BEGIN
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;
  END $$;
COMMIT;

4) Indexing for pagination

-- Endpoint uses: WHERE user_id = $1 AND created_at >= $since
-- ORDER BY created_at DESC LIMIT 20
-- Best index (PostgreSQL):
CREATE INDEX idx_events_user_created_at ON events(user_id, created_at DESC);

-- Keyset pagination example
SELECT * FROM events
WHERE user_id = $1
  AND created_at < $last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

5) Connection pooling in Node.js (pg)

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,             // concurrent connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

async function getUser(id) {
  const client = await pool.connect();
  try {
    const { rows } = await client.query('SELECT id, email FROM users WHERE id = $1', [id]);
    return rows[0];
  } finally {
    client.release();
  }
}

6) Zero-downtime migration: add NOT NULL column with default

-- Step 1: add nullable column
ALTER TABLE orders ADD COLUMN status TEXT;

-- Step 2: backfill in batches
UPDATE orders SET status = 'pending' WHERE status IS NULL AND created_at < now() - interval '1 day' LIMIT 10000;
-- repeat batch job until all rows filled

-- Step 3: set default for new rows
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

-- Step 4: add constraint
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

7) Cache-aside read with TTL

// Pseudocode
function getUserProfile(userId) {
  const key = `user:profile:${userId}`;
  const cached = redis.get(key);
  if (cached) return JSON.parse(cached);

  const row = db.query('SELECT id, email, name FROM users WHERE id = $1', [userId]);
  redis.setex(key, 300, JSON.stringify(row)); // 5-minute TTL
  return row;
}

Drills and exercises

• Draw an ER diagram for an e-commerce subset: users, products, orders, order_items, payments. Mark primary and foreign keys.
• Write a query to find the top 10 customers by revenue in the last 30 days. Add the minimal index to make it fast.
• Implement keyset pagination for a feed endpoint using a created_at cursor.
• Wrap a multi-step write (create order, add items, decrement stock) in a transaction with error handling.
• Create two composite indexes and measure impact with EXPLAIN ANALYZE on a realistic dataset.
• Perform a safe migration that adds a non-nullable column to a large table without blocking writes.
• Add a connection pool to your service and set limits that prevent database overload.
• Implement cache-aside for a hot read and verify correctness after updates by invalidating keys.

Common mistakes and debugging tips

Mini project: Feature flag service (DB-first)

Goal: Build a small service that serves feature flags per environment and user segment with fast reads and safe writes.

1. Model
   • Tables: flags, environments, variants, rules, flag_evaluations (audit).
   • Constraints: UNIQUE(flag_key, environment), FK relationships. Add CHECK for percentage between 0–100.
2. Queries
   • Get all active flags for a user: join flags + rules by environment and segment.
   • Add keyset pagination for audits.
3. Transactions
   • Updating a flag: update rule set and insert audit row atomically.
4. Indexes
   • flags(environment, active), rules(flag_id, priority), flag_evaluations(environment, created_at DESC).
5. Pooling & caching
   • Use a connection pool. Cache read results per environment for 60s; invalidate on updates.
6. Migrations
   • Ship an initial migration; later add a nullable column rollout_strategy, backfill, then enforce NOT NULL.

Additional practical project ideas

• Analytics event store: ingest, query by user and time windows, keyset paginate.
• Order management API: transactional order creation, stock reservation, payment status updates.
• Content platform: posts, tags, search by relevance with covering indexes.

Subskills

• Relational Modeling Basics
• Writing Efficient SQL Queries
• Transactions And Isolation Basics
• Indexing Concepts
• Connection Pooling
• Migrations And Schema Management
• Caching Strategies Basics
• Handling Data Consistency

Next steps

• Apply at least one indexing improvement to a real endpoint and measure with EXPLAIN ANALYZE.
• Introduce a structured migration workflow in your project and document rollback strategies.
• Add a small cache layer to one hot read and instrument hit rate and latency.