🗄️ Relational vs NoSQL Databases

What Is a Database?

A database is an organized collection of data plus the software that lets you store, retrieve, and update it reliably. That software is called a Database Management System (DBMS) — PostgreSQL and MongoDB are both DBMSs.

Think of a library. Without the Dewey Decimal System, a million books would be an unsearchable pile. The cataloguing system is what turns that pile into something you can query: "find every book by this author, published after 2020." A database does exactly this for your application's data, at a scale and speed a filing cabinet never could.

Your application never touches the raw files on disk. It sends requests to the DBMS, which handles the hard parts — concurrent access, crash recovery, indexing, permissions — and hands back exactly the data you asked for. From the moment we add a database, our app can survive restarts, serve many users at once, and answer questions about millions of records in milliseconds.

Databases split into two broad paradigms, each with a different philosophy about how data should be shaped:

• Relational (SQL) — data lives in strict tables with predefined columns; relationships are first-class.
• NoSQL ("Not Only SQL") — a family of systems that relax the table structure to gain flexibility and horizontal scale.

Relational Databases (SQL)

Relational databases, invented at IBM in the 1970s, organize data into tables (also called relations). A table is a grid: each row is one record, each column is one attribute. Before you store anything, you declare a schema — the exact columns and their types — and every row must obey it.

The vocabulary of relational data

• Primary key — a column (often id) that uniquely identifies each row.
• Foreign key — a column that references a primary key in another table, creating a relationship.
• Schema — the fixed definition of tables, columns, and types, declared up front.
• Join — an operation that combines rows from related tables into one result.
• Normalization — organizing data so each fact is stored exactly once (covered in depth two lessons from now).

Popular relational systems

What relational code looks like

You define structure with SQL, then query across relationships with a JOIN:

-- Two tables linked by a foreign key
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,          -- auto-incrementing unique id
    username   VARCHAR(50)  UNIQUE NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id           SERIAL PRIMARY KEY,
    user_id      INTEGER REFERENCES users(id),  -- foreign key -> users.id
    title        VARCHAR(200) NOT NULL,
    published_at TIMESTAMP
);

-- Combine the two tables to answer one question
SELECT users.username, posts.title
FROM users
JOIN posts ON users.id = posts.user_id
WHERE posts.published_at > '2024-01-01';

username | title
---------+------------------------
 ada     | Learning SQL
 grace   | Foreign keys explained

ACID: The Reliability Promise

Relational databases are trusted with money, medical records, and airline seats because they guarantee ACID transactions. A transaction is a group of operations treated as one indivisible unit.

The classic example is a bank transfer. Moving $100 from account A to account B is really two steps: debit A, credit B. Atomicity guarantees you can never lose the money in between — if the credit fails, the debit is undone too.

BEGIN;                                       -- start the transaction
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;                                       -- both succeed together, or ROLLBACK undoes both

NoSQL Databases

NoSQL means "Not Only SQL." These systems arose in the 2000s when web giants hit the limits of fitting one enormous relational database on one very large server. NoSQL databases trade some of the rigid structure and strong guarantees of relational systems for flexible schemas and the ability to scale horizontally — spreading data across many cheap servers.

ACID's relaxed cousin: BASE

Where relational systems chase ACID, many distributed NoSQL systems follow BASE: Basically Available, Soft state, Eventual consistency. The idea: stay fast and available even under network trouble, and let all copies of the data converge to the same value shortly after a write rather than instantly.

The four families of NoSQL

1. Document stores

Store data as flexible, JSON-like documents. Each document is self-contained and two documents in the same collection need not have identical fields. This maps beautifully to JavaScript objects.

// A MongoDB document — note the nested profile and array of posts
{
  "_id": ObjectId("5f8d5f"),
  "username": "ada",
  "email": "ada@example.com",
  "profile": {
    "age": 28,
    "interests": ["coding", "hiking"]
  },
  "posts": [
    { "title": "My first post", "likes": 12 },
    { "title": "Learning NoSQL", "likes": 34 }
  ]
}

Great for: content management, product catalogs, user profiles — anything whose shape varies or evolves. Examples: MongoDB, CouchDB, Firestore.

2. Key-value stores

The simplest model: a giant dictionary of key → value. Blazingly fast for lookups by key, but you can't query the values themselves.

// Redis: store a session, read it back, expire it in 1 hour
SET session:abc123 '{"userId":42,"role":"admin"}'
GET session:abc123
EXPIRE session:abc123 3600

Great for: caching, session storage, real-time leaderboards. Examples: Redis, Amazon DynamoDB, Memcached.

3. Wide-column stores

Store data in tables, but each row can have different columns, and columns are grouped into families optimized for huge write volumes across many machines.

Great for: time-series data, IoT sensor streams, event logging at massive scale. Examples: Apache Cassandra, HBase, ScyllaDB.

4. Graph databases

Model data as nodes connected by edges, making relationship-heavy queries ("friends of friends who like hiking") fast and natural.

// Neo4j Cypher: who are Ada's friends?
MATCH (u:Person {name: 'Ada'})-[:FRIENDS_WITH]->(friend)
RETURN friend.name

Great for: social networks, recommendation engines, fraud detection. Examples: Neo4j, Amazon Neptune, ArangoDB.

Same Data, Two Shapes

The clearest way to feel the difference is to model the same information both ways. Here is one blog user who wrote two posts.

Neither is "better." The relational version makes it trivial to ask "how many posts total across all users?" and guarantees a post can't reference a user who doesn't exist. The document version fetches a user and all their posts in a single, fast read — no join required — but duplicates structure and makes cross-user queries harder.

Choosing Between Them

Vertical vs horizontal scaling

When one server can't keep up, you have two options. Vertical scaling ("scale up") means buying a beefier machine — more CPU, more RAM. It's simple but has a ceiling and gets expensive fast. Horizontal scaling ("scale out") means adding more ordinary machines and spreading the data across them. NoSQL systems were built for this; classic relational databases find it harder because joins and ACID guarantees are tricky across many machines.

💡 Default advice: For most web apps you're learning to build, start with a relational database like PostgreSQL. It's flexible enough for the vast majority of projects, and its guarantees save you from a category of bugs you don't want to debug. Add NoSQL when a specific need appears.

Polyglot Persistence

Real production systems rarely pick just one. Polyglot persistence means using the right database for each job within a single application. Netflix, Airbnb, and Uber all mix several.

Orders go in PostgreSQL because money demands ACID. The flexible product catalog fits a document store. Sessions live in Redis for speed. "Customers who bought this also bought…" is a graph problem. Each database earns its place by doing one thing exceptionally well.

Practice & Quiz

CREATE TABLE authors (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE recipes (
    id        SERIAL PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);
CREATE TABLE ingredients (
    id        SERIAL PRIMARY KEY,
    recipe_id INTEGER REFERENCES recipes(id),
    name      VARCHAR(100),
    amount    VARCHAR(50)
);

{
  "title": "Adobo",
  "author": { "name": "Ray" },
  "ingredients": [
    { "name": "chicken", "amount": "1 kg" },
    { "name": "soy sauce", "amount": "1/2 cup" },
    { "name": "vinegar", "amount": "1/2 cup" }
  ]
}

Best Practices & Pitfalls

✅ Do

• Start with a relational database unless you have a concrete reason not to
• Let the app's access patterns and consistency needs drive the choice, not hype
• Use ACID transactions whenever data must stay correct together (transfers, inventory)
• Mix databases deliberately (polyglot persistence) when one job genuinely needs a different tool

❌ Don't

• Pick NoSQL just because it sounds modern — "schemaless" still needs a thought-out data model
• Assume NoSQL means "no rules" — you still design for how you'll read the data
• Expect eventual-consistency systems to behave like a bank ledger
• Bolt on a second database before a single well-designed one has actually hit a wall

Summary

📚 Additional Resources

• PostgreSQL — Official Tutorial
• MongoDB — NoSQL Explained
• MDN — Database (glossary)

🚀 What's Next?

You now know which kind of database to reach for. Next we learn to actually talk to a relational one: SQL Basics — creating tables, and writing SELECT, INSERT, UPDATE, and DELETE to command your data.