Concepts / One-to-Many Relationships: The Foundation of Relational Databases

One-to-Many Relationships: The Foundation of Relational Databases

The quickest test is to ask two questions: can one record on the left connect to multiple records on the right, and can one record on the right connect to multiple records on the left? If both answers are yes, the relationship is many-to-many.

  • Programming

When One-to-Many Stops Fitting

A one-to-many relationship models situations in which one record connects to multiple records of another type, while each connection flows in only one direction. A teacher can have many students, and a department can have many employees. However, some real-world connections work in both directions: one record on the left can connect to several records on the right, and one record on the right can also connect to several records on the left. That pattern is many-to-many.

Two entitiesLeft multiplicityRight multiplicityOne-to-manyMany-to-manyinspectmultiple on one sidesingle on the other sidemultiple on the other side
Can each record on both sides legitimately connect to multiple records on the opposite side, or is one side limited to a single connection?
Key point

The quickest test is to ask two questions: can one record on the left connect to multiple records on the right, and can one record on the right connect to multiple records on the left? If both answers are yes, the relationship is many-to-many.

Tracing the Multiplicity

Cardinality describes how many records can participate in a relationship. In a one-to-many relationship, multiplicity appears on only one side. A single record on the one side can connect to multiple records on the many side. In a many-to-many relationship, multiplicity appears on both sides: records in each entity can connect to multiple records in the other entity.

User ACourse 1User BCourse 2belongs tobelongs tobelongs to
Which records connect to which other records, and how many connections can each record have?

The user-and-course scenario illustrates why a one-to-many model is insufficient. A user can belong to many courses, and a course can contain many users. Neither entity is limited to a single connection on the opposite side.

TeacheroneUsermanyStudentmanyCoursemanyone-to-manymany-to-many
How does multiplicity flow between the two entities in a one-to-many relationship compared with a many-to-many relationship?

Reading ER Diagram Symbols

Entity-relationship diagrams show cardinality with symbols at the ends of the line connecting two tables. A one-to-many relationship has a single indicator at one end and a crow's foot or other many indicator at the other end. A many-to-many relationship has a many indicator at both ends. This visual difference tells you that the relationship needs a different storage design.

UsersJunction tableforeign keys to bothentitiesCoursesone-to-manyone-to-many
How are records in two entities connected when each record can relate to multiple records on the other side?
Definition

A junction table, also called an associative entity, is a third table that connects the two original tables. It contains foreign keys pointing to both original tables and changes one many-to-many relationship into two one-to-many relationships.

Why Two Tables Are Not Enough

A one-to-many relationship is straightforward to implement because the many-side table can contain a foreign key pointing to the one-side table. For example, a student table can contain teacher_id, so each student record points to one teacher. The same direct arrangement cannot represent users belonging to many courses.

If a course_id column is added to the user table, one user row can point to only one course through that column. If a user_id column is added to the course table, one course row can point to only one user through that column. Each choice captures only one connection per row, so neither choice captures the full many-to-many relationship.

Userscourse_idUsersCoursesuser_idJunction tableuser foreign key and courseforeign keyCoursesdirect foreign keysone-to-manymany-to-one
What happens to the relationship data when one record must reference multiple records without creating repeating foreign-key columns?
Worked example

Modeling Users and Courses

Users can belong to many courses, and courses can contain many users. Decide whether two original tables with direct foreign keys are sufficient.

  1. 1
    Check the user side

    A user can connect to multiple courses, so placing a single course_id in the user table cannot represent every course connection.

  2. 2
    Check the course side

    A course can connect to multiple users, so placing a single user_id in the course table cannot represent every user connection.

  3. 3
    Add the bridge

    Introduce a junction table containing foreign keys pointing to both the users table and the courses table.

  4. 4
    Read the resulting structure

    The original many-to-many relationship is now represented as two one-to-many relationships involving the junction table.

Result

Two direct foreign-key columns in the original tables are insufficient. A junction table is required to store the multiple connections.

The Junction Table Bridge

The junction table resolves the modeling problem by storing connections separately from the two original entities. One foreign key points to the first original table, and another foreign key points to the second. This allows any record in one table to connect to any number of records in the other through separate rows in the bridge table.

Key point

The junction table does not remove the many-to-many meaning. It gives that meaning a relational structure that can store each connection separately through two one-to-many relationships.

Design Mistakes to Avoid

  • Common mistake

    Assuming every relationship is one-to-many

    What it looks like

    Treating users and courses as though each user belongs to only one course or each course contains only one user.

    Why it fails

    The real connection permits multiple records on both sides.

    Do this instead

    Test multiplicity independently on both sides. If both sides can connect to multiple records, use a many-to-many design.

  • Common mistake

    Adding one foreign-key column to one original table

    What it looks like

    Adding course_id to the user table for users who can belong to many courses.

    Why it fails

    That column can store only one value per user row, so it cannot capture all course connections.

    Do this instead

    Use a junction table with foreign keys to both original tables.

  • Common mistake

    Adding one foreign-key column to each original table

    What it looks like

    Adding course_id to users and user_id to courses.

    Why it fails

    Each row still points to only one record through each column, so the two columns do not represent all-to-all connections.

    Do this instead

    Represent the relationship as two one-to-many relationships through an associative entity.

  • Common mistake

    Ignoring the ER diagram's many indicators

    What it looks like

    Reading a many-to-many line as though it had a single indicator on one end.

    Why it fails

    The symbols communicate whether multiplicity exists on one side or both sides.

    Do this instead

    Look for a many indicator at both ends when identifying a many-to-many relationship.

Test the Model

MEDIUM

A department has many employees, and each employee belongs to one department. A different scenario has users belonging to many courses while each course contains many users. Identify the relationship pattern in each scenario and state whether a junction table is needed.

Hints
  • Check whether multiplicity exists on one side or both sides.
  • For the second scenario, ask whether one direct foreign-key column can store all course or user connections.
  1. 1
    Identify the two entity types.
  2. 2
    Ask whether one record on the first side can connect to multiple records on the second side.
  3. 3
    Ask the same question in the opposite direction.
  4. 4
    Use one-to-many when multiplicity flows in only one direction.
  5. 5
    Use a junction table when both sides can connect to multiple records.

Key Takeaways

  1. 1
    A one-to-many relationship has multiplicity on one side only.
  2. 2
    A many-to-many relationship exists when records on both sides can connect to multiple records on the opposite side.
  3. 3
    In an entity-relationship diagram, a many-to-many relationship has many indicators at both ends of the connecting line.
  4. 4
    A single foreign-key column stores only one value per row, so direct foreign keys in the two original tables cannot represent every many-to-many connection.
  5. 5
    A junction table resolves the relationship by creating two one-to-many relationships, with foreign keys pointing to both original tables.

At a glance

  • Use one-to-many when multiplicity flows in only one direction.
  • Use many-to-many when records on both sides can connect to multiple records on the opposite side.
  • Many indicators at both ends of an ER diagram relationship line identify a many-to-many pattern.
  • Direct foreign keys in the two original tables cannot store all many-to-many connections because one foreign-key column stores only one value per row.
  • A junction table converts the many-to-many relationship into two one-to-many relationships.

Related Topics

Related concepts

More concepts starting with O