DataslopeRelationships Between Tables
The big idea that makes a database "relational" — how separate tables connect, and the three shapes those connections take.
We have built up tables, rows, columns, and primary keys. Now we reach the idea that gives relational databases their name and their power: tables do not live in isolation — they relate to one another.
Why split data across tables at all?
A natural first instinct is to cram everything into one giant table. Resist it. Real information has natural kinds of things — customers, orders, products — and each kind deserves its own table.
Why? Because mixing them forces duplication. If every order row also stored the customer's full name, address, and phone number, then a customer with 50 orders would have their details copied 50 times — and changing their phone number would mean fixing 50 rows.
The relational answer: keep each kind of thing in its own table, and
connect them with a shared value. Each order simply stores the
customer's id — a tiny reference — instead of copying all their
details.
How the connection works
The connection is beautifully simple. The customer's identity lives in the customers table as its primary key. An order points to that customer by storing the same id value. That pointing column is called a foreign key (we devote a full page to it later).
Read the link as: the customer_id on each order refers to the
id of a customer. Follow it, and you can travel from any order to
its customer, or gather all orders for a customer.
The three shapes of relationships
Almost every relationship between two kinds of things takes one of three shapes. Learning to recognize them is a core modeling skill.
One-to-many (the most common)
One customer has many orders; each order belongs to one customer. One country has many cities; each city is in one country.
The little crow's-foot (o{) means "many." One on the left, many on
the right.
One-to-one
One person has one passport; one passport belongs to one person. Less common, used to split rarely-needed details into a separate table.
Many-to-many
One student takes many courses; one course has many students. Both sides are "many." This shape needs a special helper table, which we will explore in the Connecting Tables section.
Read relationships as two sentences
Every relationship is really two sentences, one from each side. For customers and orders: "a customer has many orders" and "an order belongs to one customer." Saying both out loud tells you the shape — here, one-to-many.
See a relationship in action
Two tables, linked by customer_id. The query walks the link to
show each order beside the name of the customer who placed it —
without the name ever being duplicated in the orders table.
Ada appears twice in the result (she has two orders), but she is stored only once. The relationship let us combine the two tables on demand. That on-demand combining is called a join, and it gets its own deep dive soon.
Check your understanding
Why do relational databases split data into multiple related tables instead of one giant table?
Because a database can only hold a limited number of columns per table.
To avoid duplicating the same facts; each kind of thing is stored once in its own table and linked by shared values.
Because SQL cannot read a table with more than 100 rows.
To make every table look identical.
"One customer has many orders, and each order belongs to one customer." What relationship shape is this?
One-to-one.
One-to-many.
Many-to-many.
No relationship at all.
"A student can take many courses, and a course can have many students." What relationship shape is this?
One-to-many.
One-to-one.
Many-to-many.
It is impossible to model.