The Database Design Roadmap

What a database actually does, the workload types that shape every design (OLTP, OLAP, HTAP, read-heavy, write-heavy), and the requirements-first framework used for the rest of the course.

Entities, attributes and relationships; one-to-one, one-to-many and many-to-many; cardinality and optionality; ER diagrams; and the three ways to model inheritance in tables.

Super, candidate, primary, natural and surrogate keys; ID generation from sequences to UUIDv7 and Snowflake; and the constraints (NOT NULL, UNIQUE, CHECK, FK actions, deferrable, exclusion) that keep data correct.

Functional dependencies and attribute closure, 1NF through BCNF, 4NF and 5NF, lossless decomposition, deliberate denormalization with counters and summary tables, and access-pattern-first physical design.

DDL and DML, logical query order, every join type, subqueries with EXISTS and the NOT IN NULL trap, CTEs and recursive queries, window functions, set operations, data types and three-valued NULL logic.

How ACID is actually implemented, isolation levels and the anomalies each allows (lost update, write skew), pessimistic and optimistic locking, SKIP LOCKED queues, deadlocks, and MVCC snapshots and visibility.

Pages, tuples, TOAST and HOT updates; shared buffers and the write path; WAL, checkpoints and point-in-time recovery; VACUUM, freezing and wraparound; and B-tree vs LSM-tree storage engines.

B-tree, hash, GIN, GiST and BRIN indexes; composite index column order (equality, sort, range); covering, partial, expression and unique indexes; statistics and cost-based planning; and reading EXPLAIN ANALYZE.

Finding and fixing slow queries, keyset pagination, connection pooling with HikariCP and PgBouncer, JPA/Hibernate mapping and the N+1 problem, and Spring transaction propagation and pitfalls.

Primary/replica replication, lag and read-your-writes, failover and split brain; range, list, hash and time partitioning with pruning and retention; shard keys, resharding, cross-shard queries and hotspot mitigation.

Consistency models from linearizable to eventual, CAP and PACELC in practice, quorums, two-phase commit and sagas, the outbox pattern and CDC with Debezium, and idempotency keys and deduplication.

Choosing key-value, document, wide-column, graph and search stores; MongoDB document modeling; Cassandra query-first design; DynamoDB single-table design; Elasticsearch; Redis as a database; time-series and OLAP warehouses.

Cache-aside and friends with invalidation and stampede control, materialized views and summary tables, CQRS read models, polyglot persistence with one source of truth, and schema patterns: soft delete, history, temporal and audit tables.

Zero-downtime schema migrations with expand-contract, Flyway and safe DDL; backups, restore testing and PITR; RPO and RTO; active-passive and active-active regions; and hot, warm and cold data lifecycle.

Database authentication, least-privilege roles and row-level security, TLS and encryption at rest with key rotation, SQL injection defence, PII protection, audit trails and the right to deletion, and multi-tenant isolation models.

The database metrics, logs and traces that matter; read and write performance engineering; a failure catalogue with detection and recovery for every component; capacity planning from QPS to IOPS; and database cost trade-offs.

Complete schemas with keys, indexes, transactions and scaling for inventory, payments and double-entry ledgers, e-commerce, social feeds, chat, booking, ride sharing and notification platforms.

The database case-study template, a 36-question interview bank with model answers, a schema design problem bank from library to multi-tenant SaaS, hands-on PostgreSQL labs, the Java/Spring build track, and 30- and 60-day study plans.