Week 3 — Databases, CAP Theorem & Scaling

SQL vs NoSQL

	SQL (Relational)	NoSQL
Structure	Tables, fixed schema	Flexible (document, key-value, graph)
Queries	JOINs, complex filters	Simple key lookups or document scans
Transactions	ACID	Usually eventual consistency
Scale	Vertical first, then sharding	Horizontal by design
Use when	Business data, financial records, complex relations	Cache, session, logs, flexible schema

For ERP/accounting systems: SQL always. Transactional correctness beats schema flexibility.

NoSQL Types

Type	Examples	Best for
Key-value	Redis, DynamoDB	Cache, sessions
Document	MongoDB	Flexible schemas, nested data
Wide-column	Cassandra	Time-series, high write throughput
Graph	Neo4j	Relationships (social, fraud detection)

ACID

Property	Meaning
Atomicity	All-or-nothing. Transaction succeeds completely or not at all
Consistency	DB always moves from one valid state to another
Isolation	Concurrent transactions don’t interfere
Durability	Committed data survives crashes (write-ahead log)

CAP Theorem

A distributed system can only guarantee two of three:

	Consistency	Availability	Partition Tolerance
CP	✓	✗	✓
AP	✗	✓	✓
CA	✓	✓	✗ — not realistic

Network partitions always happen. So the real choice is: CP or AP?

• CP systems (PostgreSQL, ZooKeeper): refuse requests during partitions rather than serve stale data
• AP systems (Cassandra, DynamoDB): serve possibly stale data rather than go down

CAP consistency ≠ ACID consistency. CAP is about cross-node agreement. ACID is about transaction correctness within one node.

Scaling

Vertical (Scale Up)

Buy a bigger machine. Simple, no code changes, but hits physical and cost limits.

Horizontal (Scale Out)

Add more machines. Requires:

• Stateless app servers — no local state, any server handles any request
• Shared session storage — Redis or DB, not local memory
• Load balancer — distributes traffic across instances

Multi-tenant Sharding (ERP Pattern)

Tenant A → Database A (PostgreSQL instance 1)
Tenant B → Database B (PostgreSQL instance 1)
Tenant C → Database C (PostgreSQL instance 2)

Database-per-tenant sidesteps cross-shard joins entirely. Simple to reason about, easy to move a tenant to a new instance.

Practice

Design the data layer for a multi-company accounting system:

• 500 companies sharing one application server
• Financial data must never mix between companies
• Some companies have 10x the transaction volume of others

Self-check

• Why is ACID consistency different from CAP consistency?
• Your app needs to run in two data centres simultaneously. Which CAP trade-off applies?
• What makes horizontal scaling hard for a stateful application?