Relationships — How Tables Connect

Lesson 5 · taught by Pip

Now we put keys to work. Tables connect by one of them simply storing another's key. An order doesn't copy the whole customer; it just writes down the customer's number. That little pointer is a relationship, and it's how separate grids start acting like one.

Think of a coat check at a theater. You hand over your coat and get a numbered ticket. Your coat isn't glued to you all evening — it hangs on a rack, and the ticket points to it. When you want it back, the number fetches the right coat. A relationship works the same way: one table holds a ticket number that points to a row in another.

The pointer, in practice

Picture two tables. A customers table, where each customer has a key — customer 1, customer 2. And an orders table, where each order needs to know who placed it. Instead of copying the customer's whole name and address into the order, the order simply stores the customer's key in a column, say customer_id. Order #88 has customer_id 2, which means "this belongs to customer 2."

That borrowed key has a name: a foreign key. "Foreign" just means it's the key of a different table, visiting here to point back home. The primary key names a row in its own table; the foreign key is that same value, written down elsewhere, saying "look over there for the rest." One steady value, doing the linking.

One thing on one side, many on the other

The most common relationship is "one to many." One customer can have many orders, but each order belongs to just one customer. So the many side — orders — carries the pointer. Every order holds the customer's key; the customer holds nothing about orders. To find all of someone's orders, you simply gather every order row whose customer_id matches their key.

This shape shows up constantly. One author, many books. One patient, many appointments. One account, many transactions. In each, the "many" side keeps a foreign key pointing back at the single "one." Once you spot the pattern, you'll see it in nearly every system you touch.

Nothing gets copied, everything stays linked

Here's the quiet beauty: the customer's name and address still live in exactly one place, the customers table. The orders never copied them — they only kept a key. So when the customer moves, you change the one home, and all their orders still point at the right, now-updated row. The headache from lesson three is gone, and the tables are still fully connected.

That's the payoff of all the pieces together. Separate tables keep each fact in one home. Keys give each row a true name. Relationships let those tidy tables cooperate without ever duplicating a thing.

Why this matters

Relationships are what make a database more than a stack of lonely spreadsheets. They let you keep data lean and connected at the same time — the two things spreadsheets force you to choose between. This is the core of why businesses store records this way.

When someone designing a system asks "how do these relate — one to one, one to many, many to many?" they're deciding which table carries the pointer. You can now follow that conversation, and even spot when an answer feels off.

Your turn

Take two things from your world that connect — say teachers and classes, or customers and orders. Ask: is it one-to-many? Which side is the "many"? That side carries the key. Naming it out loud is exactly what a database designer does.

Next we'll finally ask the database a question — and meet the plain request called a query. 🔦