One customer can have many orders. Where does the link column go?
Options
- An
order_idcolumn in the customers table - A
customer_idcolumn in orders, the 'many' side (Correct option) - A separate database that links both tables
- Both tables store all ids as a list
Answer
A customer_id column in orders (the 'many' side)
Why
In a one-to-many relationship, the 'many' side (orders) stores a foreign key pointing to the 'one' side (customers).
Each order row has exactly one customer_id, while a customer row would need an unlimited list of order IDs, which tables do not store well.
Many-to-many (students ↔ courses) needs a third 'join table' with both IDs.
Remember
One-to-many: put the foreign key on the 'many' side.