Module 1 — Data Storage

Database Fundamentals

SQL vs NoSQL, ACID properties, and when to use what. The foundation of every system.

1The Filing System Analogy

Simple Analogy

Think of databases like organizing information:

SQL (Spreadsheet)

Like Excel with strict columns. Every row must have same structure. Easy to find related data across sheets.

NoSQL (File Folders)

Like folders with documents. Each document can have different fields. Flexible but harder to cross-reference.

2SQL (Relational) Databases

SQL Databases store data in tables with rows and columns. Tables are related through foreign keys. They follow a strict schema and support complex queries through SQL.

How Data is Organized

users table

id	name	email
1	Alice	alice@mail.com
2	Bob	bob@mail.com

orders table

id	user_id	total
101	1	$50
102	1	$30
103	2	$75

JOIN Query: SELECT users.name, orders.total FROM users JOIN orders ON users.id = orders.user_id

Popular SQL Databases

PostgreSQL

Feature-rich, JSON support, extensible

Used by: Instagram, Spotify, Uber

MySQL

Fast reads, widely used, mature

Used by: Facebook, Twitter, Netflix

SQL Server

Enterprise, Windows ecosystem

Used by: Microsoft, LinkedIn, Stack Overflow

3NoSQL Databases

NoSQL Databases don't use traditional table-based structure. They offer flexible schemas, horizontal scaling, and are optimized for specific access patterns.

Four Types of NoSQL

📄

Document Store

MongoDB, CouchDB

Store JSON-like documents. Each document can have different fields.

Best for: Content management, user profiles, product catalogs

🔑

Key-Value Store

Redis, DynamoDB, Memcached

Simple key→value mapping. Extremely fast lookups. Like a giant hash map.

Best for: Caching, sessions, real-time leaderboards

📊

Wide-Column Store

Cassandra, HBase, ScyllaDB

Rows can have different columns. Optimized for writes and time-series data.

Best for: Time-series, IoT data, analytics

🕸️

Graph Database

Neo4j, Amazon Neptune

Store nodes and relationships. Optimized for traversing connections.

Best for: Social networks, recommendations, fraud detection

4SQL vs NoSQL: Visual Comparison

Aspect	SQL	NoSQL
Schema	Fixed, predefined columns	Flexible, dynamic
Scaling	Vertical (bigger server)	Horizontal (more servers)
Relationships	JOINs across tables	Embed or denormalize
Transactions	Strong ACID support	Often eventual consistency
Query Language	Standardized SQL	Varies by database
Best For	Complex queries, relationships	Scale, flexibility, speed

5ACID Properties

ACID ensures reliable transactions in SQL databases. Critical for financial data, inventory, and any data that must be consistent.

Atomicity

All or nothing. Either entire transaction succeeds, or nothing changes.

Example: Bank transfer: Debit $100 from A AND credit $100 to B. If credit fails, debit is rolled back. No partial transfers.

Consistency

Database moves from one valid state to another. All rules/constraints are satisfied.

Example: If rule says 'balance >= 0', you can't withdraw more than you have. Transaction rejected.

Isolation

Concurrent transactions don't interfere. Each sees a consistent snapshot.

Example: Two users buying last item: Only one succeeds. The other sees 'out of stock', not oversold.

Durability

Once committed, data survives crashes, power outages, disasters.

Example: Order confirmed? Even if server crashes 1ms later, order is saved. Written to disk, not just memory.

6When to Use What

✓Choose SQL When

→Complex relationships: Orders → Products → Categories
→Need JOINs: Reports across multiple tables
→ACID required: Banking, inventory, payments
→Schema is stable: Known data structure upfront

✓Choose NoSQL When

→Massive scale: Millions of ops/second
→Flexible schema: Data structure evolves
→Simple queries: Mostly key lookups
→High write throughput: Logs, events, IoT

Interview Insight

Many modern systems use BOTH (polyglot persistence). PostgreSQL for orders/users (need ACID), Redis for sessions/cache (need speed), Elasticsearch for search (need full-text).

7Real-World Examples

Instagram

PostgreSQL (users, posts)Redis (cache, sessions)Cassandra (feed storage)

Netflix

MySQL (billing, accounts)Cassandra (viewing history)ElastiCache (session data)

Uber

MySQL (core data)Redis (real-time location)Cassandra (trip history)

8Key Takeaways

1SQL databases = structured, relational, ACID, complex queries. Start here for most apps.

2NoSQL databases = flexible, scalable, eventual consistency, specific patterns.

3ACID guarantees reliable transactions—critical for financial/inventory data.

4NoSQL types: Document (MongoDB), Key-Value (Redis), Wide-Column (Cassandra), Graph (Neo4j)

5Choose based on access patterns, not hype. SQL is often fine!

6Modern systems use multiple databases for different needs.