Unit 7 · Data that lasts
One-to-many: customers, orders and foreign keys
One customer can have many orders, and each order points back at exactly one customer through a customer_id column, a foreign key. The customer’s name lives in one place: the customers table.
The mix-up
What people often think
“The customer belongs to the order.”
What the tutor says
It runs the other way. One customer can have many orders, and each order points back at exactly one customer.
Can Priya place a second order? Can one order have two customers?
How it works
Copying the name into every order invites trouble: one typo, or Priya changing her name, and her orders no longer line up. Store her id instead and join when you need the name.
Orders do not point at each other. Each one carries its own customer_id, which is how you find every order for a customer.
Close cousins of this mix-up
The tutor watches for these in the same lesson.
“Fixing the spelling of the names solves it.”
It works until the next typo, or until Priya changes her name. Store her id instead, so her name lives in one place: the customers table.
“An order can stand on its own without a customer.”
Every order here has a customer_id. Read it, and put the order under that customer.
“A later order belongs to the earlier one from the same person.”
Orders do not point at each other. Order 103 has customer_id 1, so it sits under Priya, next to order 101.
Practise it with a tutor watching
Relationships
Link orders to customers with an id and join the two tables in one query.
Free, no account needed.