Relational Database Concepts
Understand the building blocks of a relational database — tables, rows, columns, primary and foreign keys, constraints, indexes, normalization, and transactions — so that database schemas you design or consume from PHP make sense structurally before you write a single line of SQL.
Almost every non-trivial PHP application needs to store data that outlives a single request: users, orders, articles, comments. The dominant way to do that is a relational database — MySQL, MariaDB, PostgreSQL, SQLite, and others — where data lives in tables made of rows and columns, and relationships between tables are expressed with keys rather than by nesting data inside itself. Before touching SQL syntax or PDO code, it pays to understand the vocabulary and design ideas that every relational database shares: what a table actually is, how one row points to another, what constraints and indexes are for, why we normalize data, and what a transaction guarantees. Get this mental model right and the SQL in the next lesson will read naturally instead of feeling like magic incantations.
Tables, Rows, and Columns
A relational database organizes data into tables (sometimes called relations). A table is a named, two-dimensional grid: each column defines a named field with a data type (integer, text, date, decimal, and so on), and each row is one record — one instance of whatever the table represents. A users table might have columns id, name, email, and created_at; each row in it is one specific user. Every row in a table has the same set of columns, even though individual values can be NULL (meaning "no value") where the schema allows it.
A database (sometimes called a schema, depending on the engine) is a named collection of tables, along with the views, indexes, and other objects that go with them. A single PHP application typically talks to one database containing many related tables — users, orders, products, order_items — each responsible for one kind of entity. Splitting data this way, instead of jamming everything into one giant table or one JSON blob, is what makes a database relational: the tables relate to each other through keys rather than through nesting.
Every column has a declared data type that the database enforces at write time. Common types include INT/BIGINT for whole numbers, VARCHAR(n)/TEXT for strings, DECIMAL(p,s) for exact fractional numbers like money, DATE/DATETIME/TIMESTAMP for temporal values, and BOOLEAN (or TINYINT(1) on MySQL, which has no native boolean type). Choosing the right type matters: it's not just documentation, it's what lets the engine store data compactly, validate it, sort it correctly, and index it efficiently.
• Table = a named grid of rows (records) and columns (fields with a declared type). • Database/schema = a collection of related tables that together model your application's data. • Every row in a table shares the same columns; individual cells can be NULL if the column allows it. • Column types (INT, VARCHAR, DECIMAL, DATE, ...) are enforced by the engine, not just documentation.
Primary Keys and Foreign Keys
A primary key is one column (or a small combination of columns, called a composite key) whose value uniquely identifies each row in a table — no two rows may share the same primary key value, and it can never be NULL. Most schemas use a surrogate key: an auto-incrementing integer (id INT AUTO_INCREMENT PRIMARY KEY in MySQL, id SERIAL PRIMARY KEY in PostgreSQL, id INTEGER PRIMARY KEY AUTOINCREMENT in SQLite) that has no business meaning at all — it exists purely to give every row a stable, compact identity that other tables can reference.
A foreign key is a column in one table that holds the primary key value of a row in another table, creating a link between them. If an orders table has a user_id column that stores values from users.id, then orders.user_id is a foreign key referencing users. The database can be told to enforce this relationship (a foreign key constraint): it will refuse to insert an order with a user_id that doesn't exist in users, and by default it will refuse to delete a user who still has orders (or, depending on the constraint's ON DELETE rule, it can cascade the delete, set the foreign key to NULL, or restrict it).
This is the essence of "relational": instead of duplicating a user's full name and email inside every one of their orders, you store the user's data once in users and every order simply points back to it with user_id. Need the user's email for an order? Join the two tables on that key. Change the user's email? It updates in exactly one place, and every order that references them sees the change automatically — there is nothing to keep in sync.
• Primary key: uniquely identifies each row; never NULL, never duplicated within the table. • Surrogate key: an auto-incrementing ID with no business meaning, used as the primary key by convention. • Foreign key: a column that stores another table's primary key value, creating a link between rows. • Foreign key constraints can enforce referential integrity and control what happens on delete/update (CASCADE, SET NULL, RESTRICT).
Constraints and Indexes
Constraints are rules the database enforces on column values so that invalid data can never be written, regardless of which application (or bug) is doing the writing. NOT NULL forbids a missing value in that column. UNIQUE forbids two rows from sharing the same value in that column (or combination of columns) — useful for things like email on a users table. CHECK constraints go further, enforcing an arbitrary boolean expression, such as CHECK (price >= 0) to reject negative prices. Constraints are your last line of defense: even if application-level validation has a bug, the database itself still refuses bad data.
An index is a separate, ordered data structure (commonly a B-tree) that the database maintains alongside a table so it can find matching rows quickly without scanning every row. Without an index on email, looking up a user by email means the engine checks every single row; with an index, it can jump almost directly to the match, similar to how a book's index lets you find a topic without reading every page. Primary keys and, on most engines, UNIQUE columns are indexed automatically; you add indexes explicitly on other columns you frequently filter, join, or sort by.
Indexes aren't free, though: they speed up reads but slow down writes a little (every INSERT/UPDATE/DELETE has to update the index too) and they take up additional disk space. The practical rule of thumb is to index columns used in WHERE, JOIN ... ON, and ORDER BY clauses on tables large enough that a full scan would be slow, and to avoid indexing every column "just in case".
• NOT NULL — the column must always have a value. • UNIQUE — no two rows may share the same value in that column. • CHECK — enforces an arbitrary condition, e.g. CHECK (price >= 0). • Index — a B-tree-like structure that speeds up lookups/joins/sorts at the cost of extra writes and storage. • Primary keys are indexed automatically; add indexes deliberately on columns you frequently query by.
Normalization: 1NF, 2NF, 3NF
Normalization is the process of organizing tables to minimize duplicated data and the anomalies that come with it — the same fact stored in multiple places can drift out of sync when only one copy gets updated. It's usually described as a series of "normal forms", each stricter than the last, though in everyday PHP work getting to third normal form (3NF) is almost always enough.
First normal form (1NF) requires that every column hold a single, atomic value — no comma-separated lists or repeating groups crammed into one cell. A tags column holding "php,mysql,web" violates 1NF; the fix is a separate tags table plus a join table (post_tags) linking posts to tags, so each tag is its own row.
Second normal form (2NF) applies to tables with a composite primary key and requires that every non-key column depend on the *entire* key, not just part of it. Third normal form (3NF) goes further: every non-key column must depend only on the primary key, not on another non-key column. A classic 3NF violation is storing both user_id and user_email on an orders table — the email depends on the user, not on the order, so if the user changes their email you'd have to remember to update it on every one of their orders too, and any order you miss now has a stale, wrong email.
The fix for that example is straightforward: orders keeps only user_id (the foreign key), and email lives exactly once, on users. Any query that needs an order's email joins to users at read time. This is the trade-off normalization makes explicit: fewer places for the same fact to live means fewer chances for it to become inconsistent, at the cost of needing a JOIN to reassemble the full picture.
• 1NF: every column holds one atomic value — no comma-separated lists in a single cell. • 2NF: every non-key column depends on the whole primary key (relevant for composite keys). • 3NF: every non-key column depends only on the primary key, not on another non-key column. • Before (denormalized): orders(id, user_id, user_email, total) — email duplicated per order. • After (3NF): users(id, email) + orders(id, user_id, total) — email lives once, orders reference it by user_id.
Transactions
A transaction groups multiple SQL statements into a single all-or-nothing unit of work. Consider transferring money between two bank accounts: you need to subtract from one account and add to the other. If the process crashes after the subtraction but before the addition, the money simply vanishes — unless both statements are wrapped in a transaction, in which case the database guarantees that either both happen or neither does.
This all-or-nothing guarantee is usually summarized by the ACID properties: Atomicity (the transaction happens completely or not at all), Consistency (it moves the database from one valid state to another, respecting constraints), Isolation (concurrent transactions don't see each other's half-finished work), and Durability (once committed, the change survives even a crash immediately afterward). A transaction is started, then either committed (make all its changes permanent) or rolled back (undo everything since it started) — typically rolled back automatically if an error occurs partway through.
In practice you reach for a transaction whenever a single logical operation touches more than one row or table and it would be dangerous for only part of it to succeed: placing an order (insert the order, decrement stock, record a payment), transferring funds, or any multi-step write where a partial failure would leave the data in an inconsistent state. Later lessons show this driven from PHP through PDO's beginTransaction(), commit(), and rollBack().
• Transaction: a group of statements treated as a single all-or-nothing unit. • ACID: Atomicity, Consistency, Isolation, Durability. • Commit makes the transaction's changes permanent; rollback undoes everything since it started. • Use transactions whenever one logical operation writes to more than one row/table.
Code Examples
users.email gets a UNIQUE constraint so two accounts can never share an email. orders.user_id is declared as a foreign key referencing users.id, with ON DELETE RESTRICT meaning the database refuses to delete a user who still has orders. The explicit index on orders.user_id makes 'find all orders for this user' fast even as the table grows.
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
Two writes — inserting the order and decrementing stock — must both succeed or both be undone. Wrapping them in beginTransaction()/commit(), with a rollBack() in the catch block, guarantees the database never ends up with an order that was billed but never decremented stock for, or vice versa.
$pdo->beginTransaction(); ... $pdo->commit(); ... $pdo->rollBack();
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE RESTRICT
);
CREATE INDEX idx_orders_user_id ON orders (user_id);<?php
declare(strict_types=1);
function placeOrder(PDO $pdo, int $userId, float $total, int $productId, int $qty): int
{
$pdo->beginTransaction();
try {
$insertOrder = $pdo->prepare(
'INSERT INTO orders (user_id, total) VALUES (:user_id, :total)'
);
$insertOrder->execute(['user_id' => $userId, 'total' => $total]);
$orderId = (int) $pdo->lastInsertId();
$decrementStock = $pdo->prepare(
'UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty'
);
$decrementStock->execute(['qty' => $qty, 'id' => $productId]);
if ($decrementStock->rowCount() === 0) {
// Not enough stock — undo the order insert too.
throw new RuntimeException('Insufficient stock for product ' . $productId);
}
$pdo->commit();
return $orderId;
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
}Two Related Tables: users and orders
orders.user_id references users.id — a one-to-many relationship: one user, many orders.
users orders
+----+---------+----------------+ +----+----------------+--------+-------------+
| id | name | email | | id | user_id (FK) | total | created_at |
+----+---------+----------------+ +----+----------------+--------+-------------+
| 1 | Asha | asha@mail.com |<--- |101 | 1 | 49.99 | 2026-01-04 |
| 2 | Ben | ben@mail.com | \ |102 | 1 | 12.50 | 2026-02-11 |
| 3 | Cara | cara@mail.com | \-|103 | 2 | 99.00 | 2026-03-02 |
+----+---------+----------------+ +----+----------------+--------+-------------+
^ primary key: id ^ primary key: id
^ foreign key: user_id -> users.id
Cara (id=3) has no matching row in orders — that's allowed, a user can have zero orders.
Every order's user_id MUST match an existing users.id (enforced by the FK constraint).Important Rules
A primary key uniquely identifies a row and is never NULL or duplicated.
A foreign key stores another table's primary key value to link related rows.
Constraints (NOT NULL, UNIQUE, CHECK) reject invalid data at the database level, regardless of the application.
Indexes speed up reads on the columns they cover but add overhead to writes — index what you filter/join/sort by.
Normalize to 3NF by default: each fact should live in exactly one place.
Wrap multi-statement writes that must succeed or fail together in a transaction.
Where This Shows Up
Every framework's ORM (Eloquent in Laravel, Doctrine in Symfony) is ultimately a layer over exactly these concepts — a hasMany/belongsTo relationship is just a foreign key, and a migration file's ->unique() or ->foreign()->references() calls generate the constraints described here. Understanding the underlying relational model is what lets you read a migration, design a schema from scratch, or debug a slow query, instead of only ever calling ORM methods you don't fully understand.
Storing a customer's email on every order row (orders.user_email) instead of just their user_id.
Store only orders.user_id and join to users to read the email; the email lives in exactly one row.
Duplicating data that belongs to another entity means it can drift out of sync — update the user's email and every stale copy elsewhere is now wrong.
Deleting a user with DELETE FROM users WHERE id = 3 while a foreign key from orders still points at them, expecting it to 'just work'.
Decide the correct ON DELETE behavior up front (RESTRICT to block it, CASCADE to delete their orders too, or SET NULL to keep the orders as anonymized), and let the constraint enforce it.
Without a foreign key constraint, orphaned rows (orders pointing at a user_id that no longer exists) silently accumulate and corrupt your data's integrity.
What does a foreign key constraint prevent? A. Inserting a row whose foreign key value has no matching row in the referenced table B. Two rows in the same table from having the same primary key C. A column from ever being NULL D. A query from running slowly
A. Inserting a row whose foreign key value has no matching row in the referenced table — A foreign key constraint enforces referential integrity: the value must match an existing primary key (or UNIQUE key) in the referenced table.
Why does storing a user's email on every one of their order rows violate normalization? A. It's slower to insert B. The email depends on the user, not on the order, so it's duplicated data that can drift out of sync C. MySQL doesn't allow VARCHAR columns on more than one table D. It makes the primary key non-unique
B. The email depends on the user, not on the order, so it's duplicated data that can drift out of sync — In 3NF, a non-key column must depend only on its own table's primary key. The email belongs to the user, so it should live once on users and be reached via user_id.
What is the main trade-off of adding an index to a column? A. Indexes make SELECT queries slower but INSERT queries faster B. Indexes speed up matching reads (WHERE/JOIN/ORDER BY) but add overhead to writes and use extra storage C. Indexes remove the need for a primary key D. Indexes automatically normalize the table
B. Indexes speed up matching reads (WHERE/JOIN/ORDER BY) but add overhead to writes and use extra storage — An index is extra structure the database maintains for fast lookups; every INSERT/UPDATE/DELETE has to keep it updated too, so indexes trade write speed and storage for read speed.
Design a small schema for a blog: a posts table and a comments table, where each comment belongs to exactly one post. Decide the primary key for each table, the foreign key linking them, one NOT NULL constraint, and one index you'd add and why.
posts(id PK, title NOT NULL, body, created_at); comments(id PK, post_id FK -> posts.id, body NOT NULL, created_at). Add ON DELETE CASCADE on the foreign key so deleting a post removes its comments automatically, and index comments.post_id since you'll constantly query 'all comments for this post'.
Comments need a foreign key back to the post they belong to — think about what happens to comments when a post is deleted.