🗄️ Database Concepts and Types

What Is a Database?

A database is an organized collection of data that a computer can store, search, update, and protect efficiently. From the messages you send, to your shopping history, to a bank's ledger of transactions — all of it lives in databases. Anything an app needs to remember after the user closes the tab has to be persisted somewhere, and that somewhere is almost always a database.

💡 A useful analogy: Think of a database as a digital filing cabinet. A physical cabinet has drawers holding folders holding documents. A database has tables (or collections) holding records made of fields — but with superpowers: it can find any document in milliseconds, stop two clerks from corrupting the same file, and roll back a mistake as if it never happened.

You rarely talk to the raw data files yourself. Instead you talk to a Database Management System (DBMS) — the software that sits between your application and the stored bytes, handling storage, security, concurrency, and query optimization. PostgreSQL, MySQL, MongoDB, and Redis are all DBMSs.

Why Not Just Use Files?

Before databases, applications stored data in plain files. Imagine a business keeping its customers in a text file:

# customers.csv (flat-file approach)
1,John Smith,42,New York,1985-03-12
2,Sarah Johnson,35,Chicago,1990-07-28
3,Michael Lee,29,San Francisco,1996-11-05

This works for three customers. It falls apart at three million. Flat files have no built-in way to:

• Update one field without rewriting the whole file
• Prevent bad data (duplicate IDs, malformed emails)
• Search efficiently ("find everyone in Chicago" means scanning every line)
• Handle two processes writing at once without corruption
• Relate one file's data to another's

A DBMS solves every one of these. Here's what you gain by moving from files to a real database:

Core Database Concepts

These terms come up in every backend job, tutorial, and code review. Learn them once and they pay off everywhere.

Schema — the blueprint

A schema defines how data is organized: which tables/collections exist, what fields they hold, the data types of those fields, and the constraints and relationships between them. It's the architect's plan drawn before the building goes up. Relational databases enforce their schema strictly; many NoSQL databases keep it flexible (more on that later).

Normalization — no duplication

Normalization is the practice of organizing data so each fact is stored in exactly one place. Instead of repeating a publisher's full address on every book record, you keep publishers in their own table and have each book reference it by ID. This reduces redundancy and prevents update anomalies. We dedicate the next lesson to it.

Indexes — the shortcut

An index is an extra data structure the DBMS maintains to find rows fast — exactly like the index at the back of a book. Without an index on last_name, finding "Smith" means checking every row (a full table scan). With one, the database jumps straight to the answer.

-- Create an index so lookups by email are fast
CREATE INDEX idx_users_email ON users (email);

-- Now this query can use the index instead of scanning the whole table
SELECT * FROM users WHERE email = 'jane@example.com';

ACID and Transactions

A transaction is a group of operations treated as one indivisible unit of work: either all of them succeed, or none of them do. The classic example is a bank transfer — deduct from one account, add to another. You never want the first step to happen without the second.

BEGIN;                                                    -- start the transaction

UPDATE accounts SET balance = balance - 100 WHERE id = 123;  -- deduct $100
UPDATE accounts SET balance = balance + 100 WHERE id = 456;  -- add $100

COMMIT;   -- make both changes permanent together
-- If anything failed above, we would ROLLBACK instead and undo everything.

Reliable transactions are guaranteed by four properties known by the acronym ACID:

Traditional relational databases are strongly ACID-compliant, which is why they dominate finance, healthcare, and any domain where a lost or duplicated record is unacceptable. Many NoSQL systems relax some of these guarantees in exchange for scale and speed — a trade-off we'll unpack in the NoSQL lesson.

The Major Database Types

Databases fall into two broad camps — relational (SQL) and non-relational (NoSQL) — and NoSQL itself splits into four sub-families, each optimized for a different shape of data.

Relational databases (SQL)

Data lives in tables of rows and columns with a fixed schema, and tables link to each other through keys. You query them with SQL (Structured Query Language). They shine when data is well-structured, relationships matter, and you need ACID guarantees.

• PostgreSQL — powerful, standards-compliant open-source database; the modern default choice
• MySQL / MariaDB — hugely popular for web apps
• SQLite — a whole database in a single file; perfect for mobile and small apps
• Oracle / SQL Server — enterprise-grade commercial systems

Document stores

Data is stored as flexible, JSON-like documents. Each document can have its own shape, and the structure can evolve without a migration. Great for content, user profiles, and product catalogs.

{
  "_id": "5f8d0c0b1c9d440000a7df7b",
  "username": "johndoe",
  "email": "john@example.com",
  "profile": {
    "firstName": "John",
    "lastName": "Doe",
    "interests": ["hiking", "photography", "cooking"]
  },
  "lastLogin": "2026-03-15T14:30:00Z"
}

Examples: MongoDB, CouchDB, Firebase Firestore.

Key-value stores

The simplest model: a giant dictionary of unique keys mapping to values. Blazing fast for lookups by key, with limited querying. Ideal for caching, sessions, and leaderboards.

# Redis
SET session:a1b2c3 '{"userId":1000,"expires":"2026-03-16T14:30:00Z"}'
GET session:a1b2c3
INCR pageviews:homepage        # atomic counter
EXPIRE session:a1b2c3 3600     # auto-delete after 1 hour

Examples: Redis, Amazon DynamoDB, Riak.

Column-family stores

Store data by column family across many machines, built for write-heavy workloads at enormous scale. Rows can hold different columns. Ideal for time-series and IoT data.

-- Cassandra (CQL looks like SQL but the engine is very different)
CREATE TABLE sensor_readings (
  sensor_id  uuid,
  reading_at timestamp,
  temperature float,
  PRIMARY KEY (sensor_id, reading_at)
);

Examples: Apache Cassandra, HBase, ScyllaDB.

Graph databases

Model data as nodes (entities) and edges (relationships). Traversing connections — "friends of friends", "people you may know" — is natural and fast, where the same query in SQL would need many expensive joins.

// Neo4j Cypher — create a friendship
CREATE (john:Person {name: 'John'})
CREATE (mary:Person {name: 'Mary'})
CREATE (john)-[:FRIENDS_WITH {since: '2026-01-15'}]->(mary);

Examples: Neo4j, Amazon Neptune, ArangoDB.

Choosing the Right Database

There's no single "best" database — only the best fit for your data shape, query patterns, and scale. Walk through these questions:

Hands-on Exercise

Best Practices

Summary & Quiz

📚 Further Reading

• PostgreSQL — Official Tutorial
• MongoDB — NoSQL Explained
• Neo4j — Graph Database Fundamentals
• "Designing Data-Intensive Applications" by Martin Kleppmann (book)

🚀 What's Next?

Now that you can tell the database families apart, we'll zoom into the most common one. Next lesson: Relational Database Design — modeling entities, keys, relationships, and normalization the right way.