Phase 3
Data Layer
Databases, consistency, scaling — the heart of every system.
Pause & reflect
Before you read on, answer this in your head — or on paper:
Your database is too big for one machine. You add more nodes.
How do you split the data? What happens when you add or remove a node? How do you avoid hotspots?
SQL when you need ACID, joins, relational structure. NoSQL when you need horizontal scale, flexible schema, or specific patterns (document, key-value, wide-column, graph). Many systems use both.
SQL Databases
Relational tables with rows and columns. Pre-defined schema, primary/foreign keys. Materialized views: pre-computed query results stored for fast reads; great for complex or frequent queries. N+1 problem: when the app runs N extra queries for data that could be batched — fix with eager loading, DataLoader, or optimized joins.
Indexes
Indexes speed up lookups by storing column values and pointers to rows. Trade-off: faster reads, slower writes (index must be updated). Dense index: one entry per row — fast lookup, more memory and write cost. Sparse index: entries for some rows only — less memory, faster writes, but scan may be needed after lookup. Choose based on read vs write load.
Normalization & Denormalization
Normalization removes redundancy and anomalies. 1NF: no repeating groups, atomic values. 2NF: no partial dependency on the primary key. 3NF: no transitive dependency. BCNF: every determinant is a candidate key. Denormalization: add redundant columns/tables to avoid joins — faster reads, trickier writes and consistency.
NoSQL types
Document: MongoDB, flexible JSON. Key-value: Redis, DynamoDB. Wide-column: Cassandra, Bigtable. Graph: Neo4j. Time-series: InfluxDB, Druid. Multi-model: ArangoDB, Cosmos DB — multiple models in one backend.