🏛️ Databases

Spreadsheet:
  - Rows = individual records
  - Columns = fields/attributes
  - Sheets = tables
  - Workbook = database

Database = Super-powered spreadsheet that:
  - Handles millions of rows
  - Multiple users simultaneously
  - Guarantees data integrity
  - Finds data instantly

Store users in a text file:
  Alice,alice@email.com,28
  Bob,bob@email.com,35
  Carol,carol@email.com,22

Problems:
  ✗ Finding user 50,000? Scan entire file
  ✗ Two people editing? Conflicts!
  ✗ Data validation? You're on your own
  ✗ Power outage mid-write? Corrupted data

✓ Indexes for fast lookups
✓ Concurrent access handling
✓ Data type validation
✓ Transaction safety
✓ Backup and recovery
✓ Query language (SQL)

Users Table:
  ┌────┬───────┬────────────────────┐
  │ id │ name  │ email              │
  ├────┼───────┼────────────────────┤
  │ 1  │ Alice │ alice@example.com  │
  │ 2  │ Bob   │ bob@example.com    │
  └────┴───────┴────────────────────┘

Orders Table:
  ┌────┬─────────┬────────┬────────────┐
  │ id │ user_id │ amount │ date       │
  ├────┼─────────┼────────┼────────────┤
  │ 1  │ 1       │ 50     │ Jan 15     │
  │ 2  │ 1       │ 75     │ Jan 20     │
  │ 3  │ 2       │ 25     │ Jan 22     │
  └────┴─────────┴────────┴────────────┘

user_id LINKS to Users table.
This is a RELATIONSHIP.

Without relationships:
  Store user info in EVERY order
  Alice changes email? Update EVERY order!

With relationships:
  Store user info once
  Orders link to users by ID
  Change once, reflected everywhere

Unique identifier for each row:
  ┌────────────┬───────┐
  │ id (PK)    │ name  │
  ├────────────┼───────┤
  │ 1          │ Alice │
  │ 2          │ Bob   │
  └────────────┴───────┘

No two rows can have the same primary key.

Link to another table's primary key:
  ┌────┬─────────────┐
  │ id │ user_id(FK) │
  ├────┼─────────────┤
  │ 1  │ 1           │ ← Points to Users.id = 1
  │ 2  │ 1           │
  └────┴─────────────┘

Ensures data integrity (can't order for non-existent user).

Like a book's index:
  "Show me all Alices"

  Without index: Scan all 1 million rows
  With index: Jump directly to Alices

Dramatically speeds up queries on indexed columns.

A - Atomicity
    "All or nothing"
    Transfer $100: debit AND credit both happen, or neither

C - Consistency
  "Rules are consistently enforced"
    Balance can't go negative if that's a rule

I - Isolation
    "No interference"
    Two transfers at same time don't mess each other up

D - Durability
    "Survives crashes"
    Once committed, data survives power outage

Tables with relationships
Query with SQL
Strong consistency

Examples: PostgreSQL, MySQL, SQLite
Often used for: Most applications, complex queries

JSON-like documents
Flexible schema
Horizontal scaling

Examples: MongoDB, CouchDB
Often used for: Flexible data, rapid development

Simple key → value pairs
Super fast
Limited queries

Examples: Redis, DynamoDB
Commonly used for: Caching, sessions

Nodes and relationships
Optimized for connections
Complex relationship queries

Examples: Neo4j, Amazon Neptune
Commonly used for: Social networks, recommendations

Define structure:
  users table with id, name, email columns
  id is primary key (unique identifier)
  name is required (NOT NULL)
  email is often enforced as unique

Add a row:
  id=1, name="Alice", email="alice@example.com"

Find something:
  "Give me all users with name containing 'Alice'"
  "Give me orders over $100 from last week"

Change something:
  "Change user 1's email to new@example.com"

Remove something:
  "Delete user with id 1"
  "Delete all orders older than a couple of years"

Bigger server:
  More RAM, faster CPU, more storage

Limits:
  Eventually hit hardware limits
  Single point of failure
  Expensive at the top end

More servers:
  Distribute data across machines
  Add servers as needed

Complexity:
  Data distribution (sharding)
  Consistency challenges
  Network overhead

Copies of data:
  Primary: handles writes
  Replicas: handle reads

Benefits:
  Read scalability
  Fault tolerance
  Geographic distribution

Data loss = business loss.
Automate backups. Test restores!

Slow queries on large tables.
Index columns you search by frequently.

Leads to data duplication and update anomalies.
Normalize your data (split into related tables).

Use parameterized queries (prevent SQL injection).
Limit database user permissions.
Encrypt sensitive data.