DataslopeIntroduction to Relationships
Learn why relational databases split data into tables and connect those tables with keys.
A relational database is powerful because tables can relate to each other.
Instead of storing every fact in one giant table, you store each kind of thing in its own table, then connect the tables with key values.
Why not one giant table?
Imagine an orders table that repeats the customer's details on every order.
Ada's phone number is copied in multiple rows. If it changes, you must find and update every copy. Miss one copy, and the database disagrees with itself.
The relational solution is to store each fact once.
Tables connect through keys
The customer table has a primary key: customers.id. The orders table stores a matching value: orders.customer_id.
That pointing column is called a foreign key. You will learn foreign keys in more detail later. For now, remember the picture: one table stores the identity, and another table stores that identity to create a link.
Read it as: one customer can place many orders, and each order belongs to one customer.
Relationship shape 1: one-to-many
One-to-many is the most common relationship shape.
Examples:
- One customer has many orders.
- One author writes many books.
- One city has many residents.
The key question is: can one row on the left match many rows on the right? If yes, you may have a one-to-many relationship.
Which example is a one-to-many relationship?
One passport belongs to one person, and one person has one passport.
One customer can place many orders, and each order belongs to one customer.
Many students can take many courses.
One table has one column.
Relationship shape 2: one-to-one
A one-to-one relationship means one row here matches at most one row there.
Examples:
- One person has one passport.
- One employee has one employee profile.
- One product has one detailed specification row.
One-to-one relationships are less common than one-to-many. They are often used when you want to keep optional or rarely used details in a separate table.
Relationship shape 3: many-to-many
A many-to-many relationship means many rows on each side can connect to many rows on the other side.
Examples:
- Many students take many courses.
- Many actors appear in many movies.
- Many recipes use many ingredients.
In real tables, many-to-many relationships usually need a helper table.
The helper table stores the pairs: which student is in which course.
See a relationship in SQLite
This example stores customers once and orders separately. The query joins them so you can read order totals beside customer names.
Ada appears twice in the result because she has two orders. But Ada's customer row is stored only once.
Read every relationship two ways
A relationship is easier when you say it from both directions.
This two-sentence habit helps you choose the correct relationship shape.
Check your understanding
Why do relational databases often split data across multiple tables?
To make the same fact appear in as many places as possible.
To store each kind of thing once and connect related facts with keys.
Because SQLite cannot store more than one table.
Because queries are not allowed to combine tables.
In an orders table, what does customer_id usually do?
It stores the customer's entire phone history.
It points to the matching customer's primary key value.
It forces all customers to have the same name.
It sorts orders by total automatically.
A student can take many courses, and a course can have many students. What shape is this?
One-to-one.
One-to-many.
Many-to-many.
No relationship.
Why does a many-to-many relationship usually need a helper table?
To store the database password.
To store each pairing between one row on the first side and one row on the second side.
To prevent primary keys from existing.
To make all rows identical.