If you’re building web apps with PHP, you are almost certainly using a relational database, even if you call it “MySQL” or “Postgres” and rarely think about the theory behind it. Relational databases can be one of the unseen workhorses of our technology stack, but understanding them to a greater degree will improve your value as a developer and help reduce bugs and unexpected behavior.

This article gives a practical introduction to what relational databases are, how they store data, the building blocks you will encounter every day, and the guarantees they try to provide.

What Are Relational Databases?

A relational database organizes data into named tables made of rows and columns, and uses relationships between those tables to model complex domain-specific information in a structured, consistent way. The relational part just means the data is represented in relations, which we visualize as tables.

The Core Vocabulary

As with most technology-related topics, there’s a lot of vocabulary that we need to keep track of.

Database Servers contain one or more databases. Databases are containers to hold various objects like tables, views, and stored procedures (don’t worry if those are new terms, we’re going to work up to them).

You can think of a table like a spreadsheet in that it contains columns and rows that you can use to store data. However, a table has a name, a fixed set of columns, but still allows any number of rows as long as you have the memory and storage for them.

Tables group related data. You might have users, posts, and comments, which will all contain specific information about the entity they’re built to track.

Columns will define the shape of data, with each column having a name, a type, and an optional constraint. For example, our users table has the columns id, email, name, and created_at.

Rows are individual records within the table, so each row in the users table represents one user and is referenced using something called a primary key.

Most relational databases use a text-based query language called Structured Query Language (SQL) to interact with them. It’s an extremely powerful query language, and it can do everything from creating databases and tables to performing complex retrieval operations.

The SQL statement below is what we use to create our initial users table.

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
)

Here, users is the table, id, email, name, and created_at are columns, and each inserted record becomes a row.

Primary and Foreign Keys

Primary keys and foreign keys are how relational databases express relationships between tables.

A primary key uniquely identifies a row within a table. Typical choices are an auto-increment integer or a Universally Unique IDentifier(UUID). The database will use the primary key internally for indexing and lookups, and we’ll use it to reference specific rows inside our application. For example, on a lot of sites, you might see a URL of “/users/1/”; the “1” in the URL is most likely the primary key of the user you’re trying to access.

A foreign key is a column in one table that references a column (usually the primary key) of another table and is used to express a relationship. For example, a user_id column in the posts table that references the users table id column says which user created a post.

An example of a foreign key relationship inside our table structure looks like the following:

CREATE TABLE `posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `title` TEXT NOT NULL,
  `body` TEXT,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (user_id) REFERENCES users(id)
)

This FOREIGN KEY clause tells the database that posts.user_id (user_id column in the posts table) must match an existing users.id (id [which is the primary key] column in the users table), unless the database allows nulls or you explicitly choose cascade behavior. Enforcing foreign keys maintains referential integrity, so you do not end up with posts that point to non-existent users.

For our PHP applications, when you join users and posts, the primary key and foreign key provide a fast and consistent way to match rows. Also, knowing where relationships exist helps when writing migrations, seeding data, or implementing cascade deletes.

ACID Properties

ACID is an acronym that describes important guarantees relational databases aim to provide for transactions. Transactions are sequences of operations that should be treated as a single unit and are an extremely powerful part of Relational Databases.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Each part breaks down as follows:

• Atomicity: A transaction is all or nothing. If one step fails, the whole transaction is rolled back, so your data is in a consistent state.
• Consistency: Transactions make sure the database ends in a valid state at every point during the transaction, respecting all constraints, triggers, and rules as defined by your schema and constraints.
• Isolation: Concurrent transactions should not interfere with each other in ways that break correctness.
• Durability: Once a transaction commits, its changes persist even if the database crashes.

In practice, those guarantees mean you can perform multi-step changes safely. The most common example is transferring money between accounts, which requires subtracting from one account and adding to another. Both steps must succeed, or neither should be applied.

Keep in mind that ACID is a model, and the exact behavior depends on the database engine, its configuration, and isolation level. For most web apps, the default settings provide the guarantees you need. If you start doing distributed transactions or high-concurrency financial processing, you will need to dig deeper.

Data Types and Constraints

Data types define what kind of values a column can hold. Constraints restrict those values so the database can enforce rules for you.

Common data type categories include:

• Numeric: INTEGER, BIGINT, DECIMAL, FLOAT.
• Text: VARCHAR(n), TEXT.
• Date/time: DATE, TIMESTAMP, TIMESTAMPTZ.
• Boolean: BOOLEAN.
• Binary: BYTEA or BLOB for blobs of binary data.
• UUID: Many databases provide a UUID type for globally unique identifiers.

Annoyingly, these types are not always consistent across SQL database variants, so depending on what you’re using, you should refer to the database vendor’s documentation.

Constraints you will commonly use

• NOT NULL: column must have a value.
• UNIQUE: enforces uniqueness across rows.
• PRIMARY KEY: uniqueness plus not null and often an index.
• FOREIGN KEY: enforces referential integrity between tables.
• DEFAULT: a default value if none is provided.

Example combining types and constraints:

CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `sku` VARCHAR(50) NOT NULL UNIQUE,
  `stock` INTEGER DEFAULT 0 NOT NULL,
  PRIMARY KEY (`id`)
);

Constraints are powerful because the database does the enforcement for you. Rather than relying solely on application code for validation, which often has inconsistent and distributed checks, this leads to inconsistencies when different parts of the system access the data. We can define the constraints in the database, and it will always enforce them regardless of how the data gets entered.

What You Need To Know

1. A relational database organizes data into named tables made of rows and columns
2. Come by many names, including MySQL, Postgres, MSSQL, but they all have the same underlying technology
3. Use types to define the shape of your data
4. Use constraints to enforce data consistency