Databases

An organised system for storing, retrieving, and managing data so it persists beyond a single session and can be queried efficiently.

---

What is a database?

Every application that remembers anything — your user account, your order history, a list of articles, a catalogue of products — stores that information in a database. Without a database, data exists only in the computer’s short-term memory (RAM) and vanishes the moment the server restarts.

A database does more than just store data. It organises data into a predictable structure, enforces rules about what data is valid, allows you to query data quickly (find all orders from last month, count all users in Switzerland), and handles concurrent access (multiple users reading and writing at the same time without corrupting each other’s work).

There are two major families of databases. Relational databases (SQL) organise data into tables with rows and columns, linked by relationships — like a spreadsheet system where the sheets can reference each other. Document databases (NoSQL) store data as flexible, self-contained records (similar to JSON files) — better for unstructured or rapidly changing data shapes.

In plain terms

A database is a filing cabinet with a librarian. You don’t rummage through the drawers yourself. You tell the librarian what you need (“all invoices from March” or “the customer named Dupont”), and the librarian finds it instantly because the files are organised by a system (the schema). Without the filing cabinet, every document would be a loose paper on a desk — impossible to find, easy to lose.

---

At a glance

How a database fits in a web application (click to expand)

graph LR
    FE[Frontend] -->|request| API[API]
    API -->|query| DB[(Database)]
    DB -->|results| API
    API -->|response| FE

    subgraph Server
        API
        DB
    end

Key: The frontend never talks to the database directly. Requests go through the API layer on the backend, which translates them into database queries. Results flow back the same way. The database is the final layer — it stores everything persistently.

---

How does a database work?

Databases have several core concepts. Understanding these gives you the vocabulary to discuss any database system.

1. Tables (relational) or collections (document)

In a relational database, data lives in tables. Each table represents one type of thing — users, products, orders. Each row is one record (one specific user). Each column is one attribute (name, email, created date).

id	name	email	country
1	Marie	marie@example.com	CH
2	Luca	luca@example.com	IT
3	Sara	sara@example.com	CH

In a document database, the equivalent is a collection of documents — each document is a self-contained JSON-like object:

{
  "id": 1,
  "name": "Marie",
  "email": "marie@example.com",
  "country": "CH"
}

Think of it like...

A relational table is a spreadsheet. Rows are entries, columns are fields, and every entry follows the same structure. A document collection is more like a folder of index cards — each card can have slightly different fields, and you add new fields freely.

---

2. Schemas

A schema defines the structure of your data: what tables exist, what columns each table has, what data types are allowed (text, number, date), and what rules apply (this field cannot be empty, this value must be unique).

CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    email   TEXT UNIQUE NOT NULL,
    country TEXT
);

The schema is a contract — it prevents bad data from entering the system. If someone tries to create a user without a name, the database rejects the operation.

Concept to explore

See schemas for how database structures are designed, migrated, and evolved over time.

---

3. Queries

A query is a question you ask the database. In relational databases, queries use SQL (Structured Query Language):

-- Find all Swiss users
SELECT name, email FROM users WHERE country = 'CH';
 
-- Count users per country
SELECT country, COUNT(*) FROM users GROUP BY country;
 
-- Add a new user
INSERT INTO users (name, email, country)
VALUES ('Alina', 'alina@example.com', 'DE');

SQL reads almost like English: “SELECT these columns FROM this table WHERE this condition is true.”

Think of it like...

A library search system. You type in your criteria (author, year, genre), and the system searches its catalogue and returns matching books. SQL is the language you use to express those criteria precisely.

Concept to explore

See sql for the full language — SELECT, INSERT, UPDATE, DELETE, JOINs, and more.

---

4. Relationships

The “relational” in relational databases refers to relationships between tables. Data in one table can reference data in another using foreign keys.

erDiagram
    USERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "appears in"

• A user places many orders (one-to-many)
• An order contains many order items (one-to-many)
• A product appears in many order items (one-to-many)

This structure means you store each piece of information only once (the user’s name lives in the users table, not duplicated in every order) and link records together through IDs.

Why relationships matter

Without relationships, you would duplicate data everywhere. If a user changes their email, you would need to find and update it in dozens of places. With relationships, you update it once — in the users table — and every reference automatically reflects the change.

---

5. Indexes

An index is a shortcut that makes queries faster. Without an index, the database must scan every row to find matches (like reading every page of a book to find a word). With an index, it can jump directly to the relevant rows (like using the book’s index at the back).

Indexes speed up reads but slightly slow down writes (because the index must be updated too). Choosing what to index is a key performance decision.

Concept to explore

See indexes for how indexes work internally and when to use them.

---

Why do we use databases?

Key reasons

1. Persistence. Data survives server restarts, crashes, and deployments. Without a database, everything stored in memory disappears the moment the process stops.

2. Querying power. Databases can search, filter, sort, and aggregate millions of records in milliseconds. Doing this with flat files or in-memory data would be orders of magnitude slower.

3. Data integrity. Schemas, constraints, and transactions ensure that data stays consistent. The database prevents invalid states (like two users with the same email, or an order without a customer).

4. Concurrent access. Multiple users can read and write at the same time without corrupting each other’s data. The database manages locks, transactions, and conflict resolution automatically.

---

When do we use a database?

• When an application needs to remember data between sessions (user accounts, content, settings)
• When multiple users or services need to share the same data
• When you need to search, filter, or aggregate large amounts of data efficiently
• When data integrity matters — financial records, medical data, legal documents
• When building any web application with a backend that serves dynamic content

Rule of thumb

If the data matters enough that losing it would be a problem, it belongs in a database — not in a file, not in memory, not in a spreadsheet.

---

How can I think about databases?

The library catalogue

A database is a library catalogue system.

• Each table is a section of the catalogue (books, authors, borrowers, loans)
• Each row is a single entry (one specific book)
• Each column is an attribute (title, ISBN, publication year)
• Relationships link entries across sections: a book has an author, a loan connects a borrower to a book
• Queries are searches: “all books by this author published after 2020”
• The schema is the cataloguing system itself — the rules that determine what information is recorded for each book and how entries are formatted
• Indexes are the alphabetical guides and subject headings that let the librarian find entries quickly without scanning every card

The warehouse inventory system

A database is a warehouse inventory system.

• Tables are sections of the warehouse (electronics, clothing, food)
• Rows are individual items on the shelves
• Columns are properties (SKU, name, quantity, location, price)
• Relationships connect items to suppliers and to orders
• Queries are stock checks: “how many of item X are left?” or “which items need restocking?”
• Constraints prevent impossible states: quantity cannot be negative, every item must have a supplier
• Without this system, warehouse workers would have to walk every aisle to answer even the simplest question

---

Concepts to explore next

Concept	What it covers	Status
relational-databases	Tables, rows, columns, JOINs, and the relational paradigm	stub
document-databases	Collections, documents, and the NoSQL approach	stub
sql	The query language for relational databases	stub
schemas	How database structure is defined and evolved	stub
orm	Tools that let you work with database data as code objects	stub
indexes	How databases speed up queries with indexing structures	stub
vector-databases	Databases optimised for storing and querying high-dimensional vectors by similarity	complete

Some of these cards don't exist yet

They’ll be created as the knowledge system grows. A broken link is a placeholder for future learning, not an error.

---

Check your understanding

Test yourself (click to expand)

1. Explain what a database does and why an application cannot simply store data in files or in memory.
2. Name the five core concepts of databases covered in this card and describe each in one sentence.
3. Distinguish between a relational database and a document database. When might you prefer one over the other?
4. Interpret this scenario: a web application allows two users to edit the same record simultaneously, and one user’s changes overwrite the other’s. Which database concept addresses this problem?
5. Connect databases to apis: how does the API layer protect the database from direct access by the frontend?

---

Where this concept fits

Position in the knowledge graph

graph TD
    CSM[Client-Server Model] --> FE[Frontend]
    CSM --> BE[Backend]
    CSM --> API[APIs]
    CSM --> DB[Databases]
    DB --> RDB[Relational Databases]
    DB --> DDB[Document Databases]
    DB --> SQL[SQL]
    DB --> SCH[Schemas]
    DB --> VDB[Vector Databases]
    style DB fill:#4a9ede,color:#fff

Related concepts:

• backend — the layer that reads from and writes to the database; the database never communicates with the frontend directly
• apis — the interface through which data requests reach the backend and ultimately the database
• data-modelling — the discipline of designing database structures that accurately represent real-world entities and relationships

---

Further reading

Resources

• What is a Database? (Oracle) — Foundational overview from one of the largest database companies
• Learn Relational Database Basics (freeCodeCamp) — Key concepts for beginners with practical examples
• What is SQL? Database Definition for Beginners (freeCodeCamp) — Beginner-friendly introduction to SQL and how databases use it
• SQL and Databases Explained in Plain English (freeCodeCamp) — Jargon-free explanation of relational databases and SQL
• PostgreSQL Tutorial — Step-by-step guide to learning PostgreSQL, the most popular open-source relational database