Database Design & Scaling

Databases are the backbone of almost every system. Choosing the right database, designing an efficient schema, and scaling it as your system grows are critical skills. This lesson covers SQL vs NoSQL, normalisation, sharding, replication, and the CAP theorem.

---

SQL vs NoSQL

Relational Databases (SQL)

SQL databases store data in tables with rows and columns, linked by foreign keys. They guarantee ACID properties.

┌─────────────────────────────────────────────────┐
│                  ACID Properties                  │
├──────────────┬──────────────────────────────────┤
│ Atomicity    │ All-or-nothing transactions       │
│ Consistency  │ Data always valid per constraints │
│ Isolation    │ Concurrent txns don't interfere   │
│ Durability   │ Committed data survives crashes   │
└──────────────┴──────────────────────────────────┘

Examples: PostgreSQL, MySQL, SQL Server, Oracle

NoSQL Databases

NoSQL databases sacrifice some ACID guarantees for flexibility and horizontal scalability.

Type	Data Model	Examples	Best For
Document	JSON-like documents	MongoDB, CouchDB	Flexible schemas, nested data
Key-Value	Key → value pairs	Redis, DynamoDB	Caching, session storage
Wide-Column	Column families	Cassandra, HBase	Time-series, write-heavy
Graph	Nodes and edges	Neo4j, Amazon Neptune	Relationships, social graphs