SQL Databases: When Structure Matters

📘 Chapter 4: Databases & Storage ⏱️ 9 min read 🏗️ Lesson 013

Every application needs to store data. The relational database — born in 1970 from Edgar Codd's mathematical model — remains the most widely used approach for good reason: it gives you guarantees about your data that are extremely hard to build yourself.

The Relational Model

A relational database organizes data into tables (also called relations). Each table has:

  • Columns — define the structure (name, type, constraints)
  • Rows — each row is one record
  • Primary key — uniquely identifies each row
  • Foreign keys — create relationships between tables

The power is in relationships. Instead of duplicating data, you reference it. A user's address lives in one place; every order just points to it. Change the address once, it's updated everywhere. This is normalization.

Entity-Relationship Diagram

E-Commerce Entity-Relationship Diagram users 🔑 id (PK) email name created_at password_hash orders 🔑 id (PK) 🔗 user_id (FK) status total_amount created_at shipping_address products 🔑 id (PK) name price inventory_count category order_items 🔑 id (PK) 🔗 order_id (FK) 🔗 product_id (FK) 1:N 1:N 1:N
A normalized e-commerce schema: users have orders, orders contain items, items reference products. No data duplication.

ACID Properties

The defining feature of SQL databases is ACID — four guarantees that together mean your data is always in a valid state, even when things go wrong.

Atomicity

A transaction either fully completes or fully rolls back. There's no "half done." If you're transferring $100 between accounts, either both the debit and credit happen, or neither does.

Practically: You never end up with money deducted but not credited.

Consistency

Every transaction moves the database from one valid state to another. All constraints (foreign keys, uniqueness, check constraints) are enforced.

Practically: You can't have an order referencing a user that doesn't exist.

Isolation

Concurrent transactions don't interfere with each other. Even if 1000 users buy the last item simultaneously, only one gets it.

Practically: No double-selling, no lost updates, no dirty reads (at the right isolation level).

Durability

Once a transaction commits, it's permanent — even if the server crashes immediately after. Data is written to disk (or replicated) before confirming success.

Practically: A confirmed order stays confirmed, even through power failures.

When SQL Shines

SQL databases are the right choice when:

  • Complex queries with joins — "Find all orders from users in California that include products from category X, placed in the last 30 days"
  • Transactions spanning multiple tables — Decrement inventory AND create order item AND charge payment atomically
  • Data integrity is critical — Financial systems, healthcare, e-commerce
  • Your schema is well-defined — You know the structure of your data upfront
  • Ad-hoc queries — Analysts need to ask questions you didn't anticipate

Popular Choices

Feature PostgreSQL MySQL
Best for Complex queries, extensions, data types Read-heavy workloads, simplicity
Standards compliance Excellent (most SQL-compliant) Good (some deviations)
JSON support Native JSONB with indexing JSON type (less capable)
Replication Streaming replication Battle-tested master-slave
Concurrency MVCC (excellent under writes) InnoDB MVCC (good)
Used by Instagram, Reddit, Apple Shopify, GitHub, Uber

Rule of thumb: Pick PostgreSQL when you need advanced features or complex queries. Pick MySQL when you want simplicity and proven read-heavy performance at scale.

Shopify: MySQL for Billions of Orders

Shopify processes billions of orders through MySQL. Their choice is driven by:

  • Strict ACID compliance — A merchant's inventory count must be exactly correct. Overselling is unacceptable.
  • Proven replication — MySQL's replication is battle-tested at extreme scale.
  • Operational simplicity — Their team has deep MySQL expertise built over a decade.
  • Transactional guarantees — When a customer pays, the payment, order, and inventory update all commit together or not at all.

They shard their MySQL instances (more on this in Lesson 017) but the fundamental choice of SQL is because money and inventory demand correctness.

The Scaling Challenge

SQL databases have a fundamental limitation: they're designed to run on a single machine. This creates predictable scaling walls:

Single-Node Bottleneck

  • Write throughput — All writes go to one machine. You can't easily split writes across machines while maintaining ACID.
  • Dataset size — Eventually your data won't fit on one machine's disks or memory.
  • Vertical limits — The biggest server money can buy still has a ceiling (typically ~128 cores, ~4TB RAM).

The common mitigations (in order of complexity):

  1. Vertical scaling — Bigger machine (easy, expensive, has ceiling)
  2. Read replicas — Copy data to follower nodes that handle reads (helps read-heavy workloads)
  3. Connection pooling — Reduce overhead of database connections
  4. Caching layer — Put Redis/Memcached in front to absorb repeat queries
  5. Sharding — Split data across multiple databases (complex, breaks joins)

Interactive: Build a Schema

Drag entities to the canvas and create relationships between them. Build a schema for a simple blog system.