Relational Databases Explained

Imagine you’re building an online store. You need to keep track of a few things:

• Your users, like who they are and how to email them.
• Your products, like what’s for sale and the price.
• Your orders, like who bought what.

Here’s the catch. These things aren’t separate islands, right? An order belongs to a user, and an order contains products. So you don’t just need to store data, you need to store how the data connects. That’s exactly the problem a relational database is built to solve. Let’s see how.

🎯 What is a Relational Database

So what is this thing actually? Let’s define it in plain words:

• A relational database is a way of storing data in tables, where each table is a neat grid of rows and columns, kind of like a spreadsheet.
• The special part is the word “relational”. It means the tables can be linked to each other, so a row in one table can point to a row in another.
• You talk to it using a language called SQL. SQL stands for Structured Query Language, and it’s just the way you ask the database to store, fetch, or change data.

You’ve already used plenty of these without knowing it. Some popular relational databases are:

• MySQL and PostgreSQL, both free and used by huge companies.
• SQLite, a tiny one that runs inside apps on your phone.
• Oracle and SQL Server, common in big businesses.

Keep the store example in your head. Every idea below maps back to users, products, and orders.

🧱 Tables, Rows, Columns

Everything in a relational database lives inside tables. So let’s get these three words straight first, because the rest of the tutorial builds on them:

• A table holds one kind of thing. You’d have a users table for users, a products table for products, and so on. Think of it as one sheet in a spreadsheet.
• A column is one piece of info that every row has, like a name or an email. Columns are also called fields.
• A row is one actual record, like one single user. A row is also called a record.

Here’s a tiny users table so you can picture it. Each line is a row, and the headings across the top are the columns.

See how clean that is? Every user has the same three columns, no more and no less. That fixed shape is what makes the data predictable, and predictable data is easy to search and trust.

🔑 Keys and Relationships

Now here’s the real magic of relational databases, the part that makes them “relational”. It all comes down to special columns called keys. There are two you need to know:

• A primary key is the column that uniquely identifies each row in a table. In our users table that’s the id. No two users can share the same id, so it’s a reliable way to point to exactly one row.
• A foreign key is a column in one table that holds the primary key of another table. It’s how a row says “I belong to that row over there.”

Let’s make it concrete with the store. Say we add an orders table. Each order needs to know which user placed it. So we give the orders table a user_id column, and that column holds the id from the users table:

• In orders, the user_id is a foreign key.
• It points back to the id (the primary key) in users.
• That single link is what ties an order to the person who made it.

Here’s the picture. The orders table reaches over to the users table through that key.

So if order number 50 has user_id of 1, you instantly know Alex placed it. The relationship lives right there in the data, and that’s the whole idea behind the name.

🔗 JOINs

Okay, so the data is split across tables and linked by keys. But often you want to see it together, right? Like “show me each order along with the name of the person who made it.” For that we use a JOIN:

• A JOIN is a way to combine rows from two related tables in a single query, using the key they share.
• It follows the foreign key for you and stitches the matching rows together.

First, here’s a plain query that reads just from one table. This asks for the name and email of every user.

SELECT name, email
FROM users;

Read it line by line:

• SELECT name, email says which columns you want back.
• FROM users says which table to look in.

Now let’s bring two tables together. This next query pulls each order’s total along with the name of the user who placed it, by joining orders to users on the shared key.

SELECT users.name, orders.total
FROM orders
JOIN users ON orders.user_id = users.id;

Here’s what’s happening:

• FROM orders starts with the orders table.
• JOIN users says “also bring in the users table.”
• ON orders.user_id = users.id is the matching rule. It lines up each order with the user whose id matches that order’s user_id.

So with one query you get a neat result like “Alex spent 40, Sam spent 25.” The JOIN did the lookup across both tables for you. That’s the payoff for splitting data into linked tables in the first place.

🛡️ Why Relational Databases Are Trusted

Banks, hospitals, and shops have leaned on relational databases for decades. So why do people trust them so much? It comes down to a few solid guarantees:

• They use a schema, which is the fixed plan of your tables and columns. The schema says “a user has an id, a name, and an email,” and the database enforces that. So you never accidentally save a user with no email or a price that’s actually a word.
• They avoid duplicate and inconsistent data. Because Alex lives in exactly one row in users, you update the email in one place and everything that links to Alex stays correct.
• They follow ACID rules, which keep your data safe even when things go wrong.

That word ACID is worth a quick unpack. It’s four promises bundled together:

• Atomic means a change happens fully or not at all. If money leaves one account but the deposit fails, the whole thing rolls back. No half-done mess.
• Consistent means the data always follows your rules, before and after every change.
• Isolated means two things happening at once don’t trip over each other.
• Durable means once a change is saved, it stays saved, even if the power goes out.

You’ll see ACID come up again in the SQL vs NoSQL tutorial, where we compare these guarantees against other kinds of databases.

✅ When to Use One

So when is a relational database the right pick? The short answer is: when your data has a clear shape and clear relationships. Reach for one when:

• Your data is structured, meaning every record has the same fields. Users, orders, and products all fit this nicely.
• The pieces are related, like orders belonging to users and containing products.
• You need things to be correct above all, like in banking, payments, bookings, or inventory. You really don’t want a bank to lose track of your money.

If your store needs users who place orders made of products, a relational database is a textbook fit. That’s the bread and butter of what these were built for.

⚠️ Common Mistakes and Misconceptions

A few ideas trip people up early on. Let’s clear them out:

• “Relational databases can’t scale.” This one’s outdated. Big companies run relational databases at massive scale with the right setup. They’re not the only choice for scale, but “they can’t handle it” is just wrong.
• “Always normalize to death.” Normalizing means splitting data so nothing is repeated, which is good. But splitting it into too many tiny tables can make queries slow and painful. Sometimes a little repetition is the smarter trade. We cover this balance in Database Normalization.
• “Primary and foreign keys are the same.” They’re not. A primary key identifies a row inside its own table. A foreign key sits in another table and points back to that primary key. One says “this is me,” the other says “I belong to that one.”

🛠️ Design Challenge

Try this on your own to test what you’ve got.

Design the tables for a small library app. People borrow books. Think about:

• What goes in a books table? A members table?
• You need to track who borrowed which book. Where does the foreign key go, and which primary key does it point to?
• Write a SELECT that lists each borrowed book along with the member’s name. Which two tables would you JOIN, and on which column?

Sketch the tables and the relationship on paper. This is exactly the kind of modelling you’d do at the start of any real project.

🧩 What You’ve Learned

You can now explain how relational databases organize and connect data. Here’s what you picked up.

• ✅ A relational database stores data in tables of rows and columns, like MySQL or PostgreSQL.
• ✅ A primary key uniquely identifies each row, and a foreign key links one table to another.
• ✅ JOINs combine related tables in a single SQL query using their shared key.
• ✅ A schema enforces a fixed structure, so data stays predictable and free of duplicates.
• ✅ ACID rules keep changes atomic, consistent, isolated, and durable, which is why these databases are trusted for critical data.
• ✅ They shine when your data is structured and related, like users, orders, and products.

🚀 What’s Next?

You’ve got the foundation now. Next, we’ll compare these against the other big family of databases and learn how to design tables well.

• SQL vs NoSQL shows when to pick a relational database and when something else fits better.
• Database Normalization teaches you how to split data into tables cleanly, without overdoing it.

Get these two down and you’ll be able to design solid data models for almost any app.