A one-to-many relationship connects one record on one side with multiple records on the other. Read from the opposite direction, the same relationship is many-to-one. Its maximum cardinalities are one and many; Crow’s Foot notation marks the many side with a crow’s foot. section 5.7 section 5.10
Design the relationship from clear requirements, then identify both directions. Minimum cardinalities matter too: if both sides require at least one related record, data entry can become difficult. A common adjustment is to allow zero related records on the many side. section 5.7
Use this rule when implementing a one-to-many relationship:
- Place the foreign key on the many side, the entity the crow’s foot points to. section 5.7
- Make that foreign key the primary key from the one-side entity. section 5.7
- Do not put the foreign key on the one side: it would need extra rows for additional related records. section 5.7
Users and reviews
A user can write zero to many reviews, while each review is written by one and only one user. Review is therefore the many side. Store the user entity’s primary key as a foreign key in Review, so multiple review rows can point to the same user row. section 5.7
A many-to-many relationship is different: represent it with a junction table, producing two one-to-many relationships. That junction table contains the primary keys of both original entities. section 5.10
Checking relationship drafts
For a spaceship manufacturer, brand to model and model to spaceship are identified as one-to-many. Relationship drafts generated with ChatGPT can still be wrong when requirements are scattered or interpreted incorrectly, so compare each result with the stated requirements. section 8.7
