Chapter 5: Introduction to Relational Databases — Why Handle Relationships With Tables
What you'll learn in this chapter
- Explain what "relational" means in "relational database (RDB)"
- Explain concretely what happens if you try to manage everything in a single table
- Explain how to choose between RDB and NoSQL using at least three decision criteria
You should understand Chapter 1: The Basic Concept of a Database and Chapter 4: CRUD and ACID.
What Is a Relational Database?
A relational database (RDB) is a database that manages data in the form of tables. It was proposed in 1970 by E. F. Codd and remains widely used as the mainstream choice in web development today.
Well-known RDBMSs include PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, and SQLite. A concrete product comparison is covered in Chapter 6: DBMS and SQL.
What Relationship Does "Relational" Refer To?
"Relational" means "relationship." The key point is that it can express relationships between tables. Put another way, if you're just dumping in data that has no relationships, there's no need for it to be relational.
You can see why "relationships" matter by looking at what happens if you try to manage everything in a single table.
What Happens When You Cram Everything Into One Table
The table below shows order information managed in a single table alone.
| Order ID | Customer | Customer Address | Customer Phone | Product | Product Price | Quantity |
|---|---|---|---|---|---|---|
| 1 | Yamada | Tokyo... | 090-... | PC | 100,000 | 1 |
| 2 | Yamada | Tokyo... | 090-... | Mouse | 3,000 | 2 |
| 3 | Suzuki | Osaka... | 080-... | PC | 100,000 | 1 |
Can you spot the problems?
- Duplicated customer information: Yamada's address and phone number are repeated twice
- The hassle of updating: if Yamada moves, you need to update every one of Yamada's rows
- A breeding ground for inconsistency: if you update only some of the rows, you end up with the same "Yamada" having different addresses
- Product information has the same problem: if the PC's price changes, you'd need to rewrite it on every order row
Solving This With Multiple Related Tables
This is solved by the idea of "multiple tables with relationships between them."
- Customer information is written to the
customerstable exactly once - Product information is written to the
productstable exactly once - Orders only hold references (foreign keys) to customers and products, via the
orderstable plus theorder_detailstable
If Yamada moves, updating a single row in the customers table immediately reflects the new address from every order that references it. This is the benefit of "multiple tables with relationships."
Foreign key (FK): a column that references another table's primary key. orders.customer_id is a foreign key that references customers.customer_id. Covered in detail in Chapter 10: Key Concepts.
Entity-Relationship Diagram (ER diagram): a diagram that visually represents the relationships between tables. Covered in detail in Chapter 12: E-R Diagrams.
The Basic Structure of a Table
The basic unit of a relational database is the table. A table is made up of rows (records) and columns.
customers table
+----------+----------------+---------------------+------------+
| id (PK) | name | email | created_at |
+----------+----------------+---------------------+------------+
| 1 | Taro Yamada | yamada@example.com | 2026-01-01 |
| 2 | Hanako Suzuki | suzuki@example.com | 2026-01-05 |
| 3 | Jiro Sato | sato@example.com | 2026-01-10 |
+----------+----------------+---------------------+------------+
^ ^ ^
| | |
column column 1 row = 1 record
(primary key)
Formal definitions for terms like row, column, primary key, and foreign key are covered in Chapter 7: Basic Terminology.
Strengths and Weaknesses of Relational Databases
These trade-offs are easier to understand by comparing against the alternative: NoSQL.
RDB vs. NoSQL — How to Choose
NoSQL stands for "Not Only SQL," a catch-all term for various databases not bound by RDB's constraints.
| Aspect | Relational DB | NoSQL |
|---|---|---|
| Data structure | Tabular (structured) | Flexible (JSON / graph / key-value / document) |
| Consistency model | ACID (strong consistency) | BASE (eventual consistency) |
| Scalability | Mainly vertical scaling (making one machine stronger) | Good at horizontal scaling (adding more machines) |
| Main uses | Business systems, finance, inventory management | Big data, real-time delivery |
| Well-known examples | PostgreSQL / MySQL / Oracle DB | MongoDB / Redis / Cassandra / DynamoDB |
Decision criteria
| Criterion | Choose RDB | Choose NoSQL |
|---|---|---|
| Data consistency | Strong consistency required (finance, inventory) | Some delay or inconsistency is acceptable |
| Data structure | Structure is stable (customers / orders / products) | Structure changes frequently, or is semi-structured |
| Complexity of relationships | Relationships expressed via SQL between tables | Simple key-value or graph |
| Access pattern | Lots of aggregation and search | Extremely fast, high-volume reads and writes |
If you're unsure, starting with RDB is the safe choice for a junior engineer. RDB has abundant learning material, tools, and experienced practitioners, and its design decision criteria are well established. It's common to consider NoSQL only once you've concluded that "RDB doesn't perform well enough" or "the data structure doesn't fit RDB."
A Real-World Example — Data Structure of an E-Commerce Site
Let's look at a typical table structure for an e-commerce site as an ER diagram.
It's fine if you can't read this diagram yet. It's enough to notice the "lines" connecting each table to the others. How to read and write ER diagrams concretely is covered in Chapter 12: E-R Diagrams.
Exercises
Suppose you're managing books and authors in a single table like this. List three problems.
| Book ID | Title | Author | Author Bio |
|---|---|---|---|
| 1 | The Art of Readable Code | Dustin Boswell | Lives in California... |
| 2 | The Art of Readable Code | Trevor Foucher | Formerly at Google... |
| 3 | 97 Things Every Programmer Should Know | Takuto Wada | Graduate of Tokyo Institute of Technology... |
Sample answer
Main problems:
- Duplicated book title: "The Art of Readable Code" is one book, but because it's co-authored, it's split across two duplicate rows. Despite having the same title, it's assigned a different book ID, making it ambiguous whether this represents "the same book" or "different books"
- Bloated author bios: if the same author has written multiple books, their bio is duplicated once per book. Updating the bio means updating multiple rows
- Can't express something like "one book, three authors": you could technically add more rows, but the book title ends up tripled, making it hard to keep consistent
Solution approach: split this into a books table and an authors table, and represent "which authors are involved in which book" with a junction table, book_authors (a many-to-many relationship). Covered in detail in Chapter 14: Many-to-Many.
A common mistake: "putting the authors into a single column as CSV" ("Dustin Boswell, Trevor Foucher"). It looks simple at first, but it becomes very hard to write SQL that searches by author or looks up an author's bio (you'd need to parse the string). This is the kind of problem described as "failing to satisfy first normal form," covered in Chapter 11: Normalization.
Which should the following systems start with, RDB or NoSQL? Think through the reasoning for each.
- Managing member information for a membership site (name, email, address, purchase history)
- Message history for a real-time chat (1,000+ writes per second)
- An inventory management system (products / stock quantities / receiving-and-shipping history / stocktaking)
- Temperature and humidity data flowing in at 100 records per second from IoT sensors
Sample answer
| # | Recommendation | Reason |
|---|---|---|
| 1. Member information | RDB | The data structure is stable (name, email, address) / the integrity of personal information matters / you want to express the relationship with purchase history in SQL |
| 2. Real-time chat | NoSQL (or combined) | 1,000+ writes per second is tough for RDB. Prioritize write throughput with something like MongoDB or Cassandra |
| 3. Inventory management | RDB | Inventory-count integrity translates directly into monetary value (double-shipping, stockouts) / ACID properties matter / transactions are essential |
| 4. IoT time series | NoSQL or a time-series DB | Systems optimized for large write volumes and sequential deletion of old data, like InfluxDB or TimescaleDB, are a good fit |
A common mistake: choosing NoSQL for no reason other than "NoSQL is trendy lately." RDB has more tools, more precedent, and clearer design guidance. The safe order for a junior engineer is to consider NoSQL only once a clear need for performance or scale becomes apparent.
Alternative approach (hybrid): in large systems, it's common to combine both by use case — "master data in RDB, logs and caching in NoSQL" (for example, stock quantities in PostgreSQL, product-search caching in Redis).
Summary
Here's a recap of what this chapter covered.
- A relational database is a mechanism for managing data as "multiple tables with relationships"
- Cramming everything into a single table breeds data duplication, update overhead, and inconsistency
- Foreign keys and primary keys are the tools for expressing relationships (details in Chapters 7 and 10)
- Choosing between RDB and NoSQL comes down to "consistency / structural stability / complexity of relationships / access patterns"
- If you're unsure, starting with RDB is the safe choice for a junior engineer
The next chapter looks at the mechanism for operating a database (the DBMS) and its common language (SQL).