The One To Many Relationship

The One To Many Relationship

The one to many relationship is the workhorse of relational schemas. One parent row relates to many child rows, while each child belongs to exactly one parent.

Examples are everywhere:

• One customer has many orders
• One order has many line items
• One author writes many posts

The rule for modeling it is simple. Put the foreign key on the many side. The child table gets a column that references the parent primary key. There is no extra table involved.

This works because each child has exactly one parent, so a single foreign key column can hold that parent reference. The parent never stores a list of children. Instead you find the children by querying the child table for rows whose foreign key matches the parent.

A common beginner mistake is trying to store a comma separated list of child ids in the parent. This breaks referential integrity, blocks indexing, and makes joins impossible. Always invert it and place the reference on the child.

Whether the foreign key may be null decides if the relationship is mandatory or optional. A required parent uses a not null foreign key.

Key idea

Model one to many by placing a foreign key on the many side that references the single parent, never a list of ids on the parent.