DataslopeOne-to-Many Relationships
How to model the most common relationship pattern, where one parent row connects to many child rows through a foreign key on the many side.
A customer can place many orders. An author can write many books. A post can receive many comments. In each case, one row on the one side is connected to several rows on the many side.
That shape is a one-to-many relationship, and it is the pattern you will use more than any other in relational design.
The basic shape
Read the diagram from left to right: one customer may place zero, one, or many orders. Each order belongs to one customer.
The symbols matter. || means exactly one customer on that side.
o{ means zero or many orders on the other side. The relationship is
not saying every customer has an order; it is saying every order, if it
exists, points back to one customer.
Other examples have the same shape:
The nouns change, but the design decision is the same: the child row stores a reference to its parent.
The foreign key lives on the many side
A foreign key is the column that stores the primary key of the related row. In a one-to-many relationship, that foreign key belongs on the many side.
Each order needs only one customer_id, because each order belongs to
one customer. The customer row does not need a list of order ids.
That would break the table shape: a cell would have to hold many
values, and every new order would require editing the parent row.
The many side can repeat the same parent id safely. That repetition is not a duplication problem; it is the relationship itself.
Quick check
In a one-to-many relationship between customers and orders, where should the foreign key usually go?
On customers, as a comma-separated list of order ids.
On orders, as a customer_id column pointing to customers.id.
On both tables, with each row copying all columns from the other.
On neither table, because joins can guess the relationship.
Building and querying a one-to-many
Here is the pattern in PostgreSQL. The orders.customer_id column
references customers.id, so every order must point at a real
customer.
The join follows the arrow from child to parent: orders.customer_id
points at customers.id. One customer can appear in several result
rows because that customer has several orders.
Parent rows and child rows
Designers often call the one side the parent and the many side the child. The child depends on the parent for meaning.
The parent can exist without children: a new customer may not have ordered yet. But a child usually should not exist without a parent: an order with no customer is an orphan. The foreign key prevents that broken design.
Check your understanding
What does customers ||--o{ orders mean in an ER diagram?
Each customer must have exactly one order.
One customer can be related to zero, one, or many orders.
One order can belong to many customers.
Customers and orders are unrelated tables.
Why is customers.order_ids a poor way to store a customer's orders?
Because PostgreSQL cannot store text values.
Because one cell would contain a list of many values, making the relationship hard to validate and query.
Because primary keys are optional in parent tables.
Because order ids should be random words instead of numbers.
In an authors-and-books design, which column most likely represents the one-to-many relationship?
authors.book_titles
books.author_id
authors.author_id
books.all_author_names