Cơ sở dữ liệu quan hệRelational Databases

Part of the Backend Roadmap knowledge base.

Overview

A relational database stores data as a set of tables (relations), where each table holds rows (records) described by a fixed set of columns (attributes). Tables are linked to each other through keys, and you query and modify the data with SQL (Structured Query Language). This model — proposed by Edgar F. Codd in 1970 — has been the default way to store transactional business data for five decades because it combines a simple mental model (spreadsheets that reference each other) with strong mathematical foundations (relational algebra) and hard correctness guarantees (ACID transactions).

For a backend engineer, the relational database is usually the system of record: the authoritative, durable store of truth that everything else (caches, search indexes, analytics pipelines) is derived from. Getting the schema, the queries, and the transactional behavior right matters more than almost any other backend decision, because data outlives code — you will rewrite your service three times before you migrate your users table.

This note covers the relational model, SQL, ACID and transactions, isolation levels, normalization, indexing, and a survey of the major relational database management systems (RDBMS). Deeper cross-database engineering topics — ORMs, schema migrations, replication, sharding, and horizontal scaling — live in Database Design & Scaling. For non-tabular stores see NoSQL Databases, and for reducing database load see Caching.

Fundamentals

The relational model

The core idea: model your domain as relations (tables) and the relationships between them, then let a declarative query language do the retrieval. You describe what you want, not how to fetch it — the database’s query planner figures out the execution.

Two rules distinguish a relation from a spreadsheet:

1. Rows are unordered and unique — there is no “row 5”; you identify a row by its values (via a key), not by position.
2. Each cell holds one atomic value — no lists or nested tables inside a single cell (that’s the “First Normal Form” rule, below).

Keys

Keys are how rows are identified and how tables reference one another.

• Primary key (PK) — one or more columns that uniquely identify each row. Cannot be NULL, must be unique. Every table should have one. A surrogate key is a synthetic PK with no business meaning (an auto-incrementing id or a UUID); a natural key is a real-world unique attribute (an ISO country code). Surrogate keys are usually preferred because business values change.
• Foreign key (FK) — a column that references the primary key of another table, enforcing referential integrity: you cannot insert an order for a customer_id that doesn’t exist, and (depending on the rule) you cannot delete a customer who still has orders.
• Candidate key — any column set that could serve as the PK (is unique + minimal). One is chosen as PK; the rest are alternate keys.
• Composite key — a key made of more than one column (common in join tables, e.g. (order_id, product_id)).
• Unique key / constraint — enforces uniqueness on a column that is not the PK (e.g. email).

CREATE TABLE customers (
    id          BIGSERIAL PRIMARY KEY,          -- surrogate key
    email       VARCHAR(255) NOT NULL UNIQUE,    -- alternate/candidate key
    full_name   VARCHAR(255) NOT NULL,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    customer_id  BIGINT      NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    total_cents  INTEGER     NOT NULL CHECK (total_cents >= 0),
    status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Relationships

Foreign keys express three kinds of relationships:

-- Many-to-many via a junction table
CREATE TABLE enrollments (
    student_id BIGINT NOT NULL REFERENCES students(id),
    course_id  BIGINT NOT NULL REFERENCES courses(id),
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (student_id, course_id)   -- composite PK prevents duplicate enrollment
);

Schema

A schema is the structure of the database: the tables, columns, types, and constraints. In PostgreSQL and SQL Server, “schema” is also a namespace inside a database (e.g. public.users, billing.invoices) used to group objects and manage permissions. Relational databases are schema-on-write: the shape is defined up front and enforced on every insert, which catches bad data early — the opposite of the schema-on-read approach common in NoSQL.

Data types (representative)

Key Concepts

SQL: DDL vs DML (and friends)

SQL is split into sub-languages by purpose:

(Note: TRUNCATE is sometimes classified as DDL because it’s fast and typically not transactional/row-logged; behavior varies by engine.)

SELECT: the workhorse

Logical evaluation order matters more than the written order. SQL is written SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but it’s evaluated roughly:

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

This is why you can’t use a SELECT column alias in WHERE (the alias doesn’t exist yet), but you can in ORDER BY.

SELECT status, COUNT(*) AS order_count, SUM(total_cents) AS revenue_cents
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY status
HAVING SUM(total_cents) > 100000     -- filter groups (aggregates)
ORDER BY revenue_cents DESC
LIMIT 10;

• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX. COUNT(*) counts rows; COUNT(col) skips NULLs.

JOINs

A JOIN combines rows from two tables based on a related column.

-- Customers and their total spend, including customers with zero orders
SELECT c.id, c.full_name, COALESCE(SUM(o.total_cents), 0) AS lifetime_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.full_name
ORDER BY lifetime_cents DESC;

The LEFT JOIN + COALESCE pattern is how you keep rows that have no matching child (an INNER JOIN would drop customers with no orders).

Subqueries and CTEs

A subquery is a query nested inside another. A correlated subquery references the outer query and runs per outer row.

-- Scalar subquery: customers who spent above the overall average order value
SELECT full_name
FROM customers c
WHERE (SELECT AVG(total_cents) FROM orders o WHERE o.customer_id = c.id)
      > (SELECT AVG(total_cents) FROM orders);

-- CTE (Common Table Expression) — often clearer than nested subqueries
WITH customer_spend AS (
    SELECT customer_id, SUM(total_cents) AS spend
    FROM orders
    GROUP BY customer_id
)
SELECT c.full_name, cs.spend
FROM customer_spend cs
JOIN customers c ON c.id = cs.customer_id
WHERE cs.spend > 500000;

-- Window function: rank orders per customer without collapsing rows
SELECT id, customer_id, total_cents,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth_order
FROM orders;

Prefer CTEs and window functions over deeply nested correlated subqueries — they read better and often plan better. EXISTS / IN / NOT EXISTS are the standard ways to test membership; NOT EXISTS is usually safer than NOT IN because NOT IN behaves surprisingly when the list contains NULL.

ACID properties

ACID is the set of guarantees a relational database makes about transactions — units of work that must succeed or fail as a whole.

The canonical example is a bank transfer: debit account A and credit account B. Atomicity guarantees you never debit without crediting; Consistency enforces that no balance goes negative; Isolation stops another transaction from reading the money “in transit”; Durability ensures that once the app is told “success”, the transfer survives a crash.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- both apply atomically; ROLLBACK would undo both

Transactions & isolation levels

Isolation is a spectrum: stronger isolation prevents more anomalies but reduces concurrency (and can increase locking/deadlocks). The SQL standard defines four levels by which read anomalies they permit.

The read anomalies:

• Dirty read — reading another transaction’s uncommitted changes.
• Non-repeatable read — re-reading the same row in one transaction returns different values because another transaction committed an UPDATE in between.
• Phantom read — re-running the same range query returns new rows because another transaction committed an INSERT/DELETE matching the predicate.
• Lost update / write skew — two transactions read-then-write and one overwrites the other’s change (write skew is subtler: two reads of overlapping data lead to a jointly-invalid result).

Practical guidance: Read Committed is the right default for most OLTP work. Reach for Serializable when correctness under concurrency is critical (financial invariants, inventory) and be ready to retry on serialization failures. To prevent lost updates without full serializable, use explicit locking (SELECT … FOR UPDATE) or optimistic concurrency (a version column).

-- Pessimistic locking: lock the row so no one else can update it until we commit
BEGIN;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  -- ... compute new balance in app ...
  UPDATE accounts SET balance = 50 WHERE id = 1;
COMMIT;

Locking and deadlocks

To provide isolation, databases take locks — shared (read) locks and exclusive (write) locks — on rows, ranges, or tables. Modern engines (PostgreSQL, Oracle, MySQL/InnoDB) use MVCC (Multi-Version Concurrency Control): readers see a consistent snapshot without blocking writers, and writers don’t block readers, which dramatically reduces contention.

A deadlock happens when transaction A holds a lock B wants, and B holds a lock A wants — a cycle. The database detects this and aborts one transaction (a “deadlock victim”) with an error; the app must retry it. Reduce deadlocks by:

• Acquiring locks in a consistent order across your codebase (e.g. always lock accounts by ascending id).
• Keeping transactions short — don’t hold locks across network calls or user think-time.
• Using the lowest isolation level that’s correct.

Normalization

Normalization organizes columns and tables to reduce redundancy and prevent update/insert/delete anomalies. Each normal form is a stricter rule built on the previous one.

Consider an unnormalized orders table:

Problems: the products cell isn’t atomic, Alice’s email is duplicated (update anomaly — change it in one row and it’s inconsistent), and you can’t record a customer with no order (insert anomaly).

• 1NF (First Normal Form) — atomic values, no repeating groups, each row unique. Split “Pen, Notebook” into separate rows/tables.
• 2NF — 1NF and every non-key column depends on the whole primary key (removes partial dependency; relevant when the PK is composite).
• 3NF — 2NF and no non-key column depends on another non-key column (removes transitive dependency). Customer email depends on the customer, not the order → move it to customers.
• BCNF (Boyce–Codd) — a stricter 3NF: every determinant must be a candidate key. Handles rare edge cases 3NF misses.

Normalized result:

customers(id PK, name, email)
products(id PK, name, price_cents)
orders(id PK, customer_id FK, created_at)
order_items(order_id FK, product_id FK, quantity, PRIMARY KEY(order_id, product_id))

Now the email lives in exactly one place, and any order can reference any customer and any products.

Rule of thumb: normalize to 3NF by default. It’s the sweet spot — enough to avoid anomalies without over-fragmenting.

When to denormalize: deliberately introduce redundancy for read performance when normalized JOINs are too slow at scale — e.g. a cached order_total_cents column, a comment_count on posts, or a reporting table. Denormalization trades write complexity (you must keep copies in sync) for read speed. Only do it after measuring, and document the invariant. More on this trade-off in Database Design & Scaling.

Indexes

An index is a separate data structure that lets the database find rows without scanning the whole table — like the index at the back of a book. The default index type is the B-tree (balanced tree), which keeps keys sorted and supports equality (=), range (<, >, BETWEEN), and prefix/ORDER BY lookups in O(log n) instead of O(n).

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Now "WHERE customer_id = 42" is a fast index lookup instead of a full table scan.

How it speeds queries: without an index, WHERE email = 'a@x.com' forces a sequential (full table) scan, reading every row. With a B-tree on email, the database walks the tree to the exact leaf — a handful of page reads even on millions of rows.

Composite (multi-column) indexes follow the left-prefix rule: an index on (a, b, c) can serve queries filtering on a, a,b, or a,b,c, and can help ORDER BY a, b — but not a query filtering only on b. Column order should match your most selective/common predicates.

CREATE INDEX idx_orders_cust_created ON orders(customer_id, created_at);
-- Serves: WHERE customer_id = 42 ORDER BY created_at
-- Does NOT serve: WHERE created_at > '2026-01-01'  (created_at isn't the leading column)

Covering index: if an index contains all columns a query needs, the database answers entirely from the index without touching the table (an index-only scan). PostgreSQL supports INCLUDE for non-key payload columns:

CREATE INDEX idx_orders_cust_covering
  ON orders(customer_id) INCLUDE (total_cents, status);
-- SELECT total_cents, status FROM orders WHERE customer_id = 42;  -> index-only scan

Other index types (engine-dependent): Hash (equality only), GIN (full-text, JSONB, arrays), GiST/SP-GiST (geometric, ranges), BRIN (huge append-only tables), and partial indexes (WHERE status = 'active') and expression indexes (ON lower(email)).

When NOT to index:

• Every index slows down writes — each INSERT/UPDATE/DELETE must maintain every index, and indexes consume disk and cache.
• Low-cardinality columns (e.g. a boolean is_active where 90% are true) — the index rarely helps because it doesn’t narrow results much (unless it’s a partial index on the rare value).
• Small tables — a full scan is already cheap.
• Columns wrapped in functions in the WHERE clause (WHERE lower(email) = …) won’t use a plain index — you need an expression index.
• Redundant indexes — an index on (a) is redundant if you already have (a, b).

EXPLAIN shows the query planner’s chosen execution plan; EXPLAIN ANALYZE actually runs it and reports real timing and row counts. This is the primary tool for diagnosing slow queries.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Read the output for Seq Scan (bad on a large table — usually a missing index) vs Index Scan / Index Only Scan (good), and compare the planner’s estimated rows against actual rows — a large gap means stale statistics (run ANALYZE) or a query the planner can’t estimate well.

Survey of major RDBMS

Choosing: for a new backend, PostgreSQL is the safe default — free, powerful, and it scales with you. Pick MySQL/MariaDB for existing ecosystems or ops familiarity, SQL Server/Oracle when enterprise support/tooling or an existing investment dictates it, and SQLite for embedded, local, on-device, or test databases.

Best Practices

• Always define a primary key on every table; prefer a surrogate BIGINT/UUID key.
• Use foreign keys to enforce referential integrity — let the database protect your data, don’t rely only on application code.
• Normalize to 3NF first, then denormalize deliberately and only after measuring a real read bottleneck.
• Pick the right types: NUMERIC/DECIMAL for money (never floats), TIMESTAMPTZ in UTC for time, and constrain with NOT NULL, CHECK, UNIQUE.
• Wrap multi-statement changes in transactions, keep them short, and acquire locks in a consistent order to avoid deadlocks; retry serialization/deadlock errors.
• Index for your query patterns, not blindly. Index FK columns and frequent WHERE/JOIN/ORDER BY columns; respect the left-prefix rule; remove unused/redundant indexes.
• Profile with EXPLAIN ANALYZE before and after adding an index; keep table statistics fresh (ANALYZE).
• Never build SQL by string concatenation — use parameterized queries / prepared statements to prevent SQL injection (see security).
• Avoid SELECT * in application code — name columns so schema changes don’t silently break you and covering indexes can apply.
• Beware N+1 queries (one query per row in a loop) — use JOINs or batched IN (...) lookups instead. This bites hardest through ORMs; see Database Design & Scaling.
• Set the right isolation level consciously; don’t assume the default matches another engine’s default.
• Back up and test restores. An untested backup is not a backup. Use point-in-time recovery for production.
• Offload heavy reads with a cache (Caching) and reach for NoSQL only when the access pattern genuinely doesn’t fit a relational model.

References

• PostgreSQL Documentation — the gold standard for SQL and RDBMS internals
• PostgreSQL: Transaction Isolation
• PostgreSQL: Indexes
• Use The Index, Luke! — a deep, practical guide to SQL indexing and performance
• MySQL Reference Manual
• SQLite: How it works & When to use
• Wikipedia: Database normalization
• Wikipedia: ACID