Concepts / Entity-Relationship Diagrams: Visualizing Database Structure

Entity-Relationship Diagrams: Visualizing Database Structure

A many-to-many relationship exists when records on both sides can connect to multiple records on the opposite side, unlike one-to-many where multiplicity flows in only one direction.

  • Programming
Interactive lab

Try it: From an ER Diagram to Tables

How the cardinality on an entity-relationship diagram decides the schema: each entity becomes a table with a primary key, a one-to-many relationship puts the foreign key on the many side, and a many-to-many relationship needs a junction table — and how sample rows that do not fit a too-narrow cardinality are rejected.

How it works

  1. Ask the cardinality question from both sides: can one row here link to many rows there, and the other way round? Many on both sides is many-to-many.
  2. Each entity becomes a table with an INTEGER PRIMARY KEY id.
  3. One-to-many: the many side gets a foreign-key column referencing the one side's primary key. One-to-one: that foreign key is also UNIQUE.
  4. Many-to-many: one foreign-key column holds one value per row, so a junction table with a foreign key to each side (and a composite primary key) stores every pairing — two one-to-many relationships.
  5. Load the sample rows: a second link for the same row would need a second row with the same id, so SQLite rejects it (UNIQUE constraint failed); junction rows always fit.

Default run (17 steps): Model: Customer, Order, Product with Customer places Order (1:N) and Order contains Product (M:N). … Every active link is stored: the schema produced by the mapping rules can hold these rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Three entities with one text attribute each and two relationships; participation (optional vs mandatory) is not modelled, so foreign keys allow NULL. The one-to-one rule (UNIQUE foreign key on the second entity) is the common textbook mapping.

Educational simulation

Loading the simulation…

When One Side Is Not Enough

A one-to-many relationship works when one record connects to multiple records of another type, but the connection multiplies in only one direction. A teacher can have many students, and a department can have many employees. Some real-world scenarios are different: a record on the left can connect to many records on the right, and a record on the right can also connect to many records on the left. That pattern is a many-to-many relationship.

What do you think happens?

A user can belong to many courses, and each course can contain many users. Which relationship pattern describes this situation?

  • One-to-one

  • One-to-many

  • Many-to-many

Reveal answer
Answer:

Many-to-many

Multiplicity exists in both directions: one user can connect to multiple courses, and one course can connect to multiple users.

Reading Many Indicators

In an entity-relationship diagram, cardinality is shown with symbols at the ends of the line connecting two tables. A one-to-many relationship has a single line indicator at one end and a crow's foot or other many indicator at the opposite end. A many-to-many relationship has a many indicator at both ends. The two indicators communicate that multiplication occurs from either entity toward the other.

Usermany usersCoursemany coursesmanymany
How can one record in each entity connect to multiple records in the other entity?

Comparing Relationship Direction

Relationship patternMultiplicityDiagram indicatorsMeaning
One-to-manyOne side connects to many; the other side is limited to one related recordSingle indicator at one end and many indicator at the otherMultiplicity flows in one direction
Many-to-manyBoth sides can connect to multiple records on the opposite sideMany indicator at both endsMultiplicity flows in both directions

The key distinction is whether multiple connections are possible from one side only or from both sides.

TeacheroneUsersmanyStudentsmanyCoursesmanyone to manymany to manymany to many
What is the difference between how relationships multiply in a one-to-many pattern and in a many-to-many pattern?

Testing the Real-World Rule

  1. 1
    Choose one record from the left-side entity and ask whether it can connect to multiple records on the right.
  2. 2
    Choose one record from the right-side entity and ask whether it can connect to multiple records on the left.
  3. 3
    If only the first answer is yes, the relationship follows a one-to-many pattern.
  4. 4
    If both answers are yes, the relationship is many-to-many and needs a junction table.
Left recordmultiple right records?One-to-manyone yesRight recordmultiple left records?Many-to-manyboth yesyesnoyesno
Can each record on both sides participate in multiple connections, or is one side limited to a single related record?
Worked example

Users and Courses

Determine whether the relationship between users and courses is one-to-many or many-to-many.

  1. 1
    Check the user side

    A user can belong to many courses.

  2. 2
    Check the course side

    A course can contain many users.

  3. 3
    Compare both answers

    Because both entities can connect to multiple records of the opposite type, the relationship is many-to-many.

Result

Users and courses require a many-to-many relationship.

Why One Foreign Key Fails

A one-to-many relationship is straightforward to implement by placing a foreign-key column on the many side. For example, a student table can have a teacher_id column, so each student record points to one teacher. That arrangement does not represent a many-to-many relationship. If a course_id column is added to the user table, each user can point to only one course. If a user_id column is added to the course table, each course can point to only one user. Neither design stores all the connections when users belong to many courses and courses contain many users.

Usercourse_idUseruser keyCourseuser_idJunction tabletwo foreign keysCoursecourse keydirect foreign keysforeign keyforeign key
Where would multiple related foreign-key values be stored, and why does a single foreign-key column fail to represent all connections?

Breaking the Relationship into Two

A junction table, also called an associative entity, resolves the many-to-many relationship. It is a third table placed between the two original tables. The junction table contains foreign keys pointing to both original tables. This creates two separate one-to-many relationships: records from the first original table can connect to many rows in the junction table, and records from the second original table can also connect to many rows in the junction table. Through those rows, any record in one original table can connect to any number of records in the other.

Useroriginal tableJunction tableforeign keys to bothCourseoriginal tableone to manyone to many
What changes when a direct many-to-many connection is replaced by two one-to-many relationships through an associative table?
Worked example

Representing Course Memberships

Represent the many-to-many relationship in which users belong to many courses and courses contain many users.

  1. 1
    Keep the original entities

    Retain the User and Course tables as the two entities being connected.

  2. 2
    Add the bridge

    Introduce a junction table between User and Course.

  3. 3
    Add both references

    The junction table contains a foreign key pointing to User and a foreign key pointing to Course.

  4. 4
    Read the new relationships

    User connects to many junction-table rows, and Course connects to many junction-table rows. Together, these two one-to-many relationships represent the original many-to-many connection.

Result

The junction table stores the connections between users and courses without forcing either original table to store multiple related values in one foreign-key column.

Common Modeling Mistakes

  • Common mistake

    Treating a many-to-many relationship as one-to-many.

    What it looks like

    Adding course_id to the user table when each user can belong to many courses.

    Why it fails

    A single foreign-key column can store only one value per row, so the design cannot capture all course connections for a user.

    Do this instead

    Recognize the many-to-many pattern and introduce a junction table with foreign keys to both original tables.

  • Common mistake

    Looking at only one side of the relationship.

    What it looks like

    Observing that one user can connect to many courses but not checking how many users can connect to one course.

    Why it fails

    A relationship is many-to-many only when multiplicity exists in both directions.

    Do this instead

    Ask the cardinality question from both sides before choosing the relationship pattern.

  • Common mistake

    Drawing many at only one end of a many-to-many relationship.

    What it looks like

    Using a single many indicator between users and courses.

    Why it fails

    Many at one end represents one-to-many, not many-to-many.

    Do this instead

    Place a many indicator at both ends of the direct relationship when showing the conceptual many-to-many pattern.

  • Common mistake

    Assuming the junction table is unnecessary because both original tables already have keys.

    Why it fails

    Primary and foreign keys alone do not provide a direct way for one row to store multiple related foreign-key values.

    Do this instead

    Use an associative entity containing foreign keys that point to both original tables.

Design Check Before Implementation

MEDIUM

A department has many employees. Each employee belongs to one department. Identify the cardinality pattern, then compare it with a scenario in which users belong to many courses and courses contain many users. For each scenario, state where multiplicity appears and whether a junction table is needed.

Hints
  • Check whether one employee can belong to multiple departments in the stated scenario.
  • Check both directions for the users-and-courses scenario.
  • A junction table resolves a many-to-many relationship by creating two one-to-many relationships.

What do you think happens?

A department has many employees, while each employee belongs to one department. Is this relationship many-to-many?

  • Yes, because the department has many employees

  • No, because multiplicity occurs in only one direction

Reveal answer
Answer:

No, because multiplicity occurs in only one direction

This is a one-to-many relationship. A many-to-many relationship requires records on both sides to connect to multiple records on the opposite side.

The Modeling Pattern

  1. 1
    A one-to-many relationship has multiplicity in one direction; a many-to-many relationship has multiplicity in both directions.
  2. 2
    Entity-relationship diagrams show a many-to-many relationship with a many indicator at both ends of the connecting line.
  3. 3
    A single foreign-key column stores only one value per row, so direct foreign-key placement cannot represent every many-to-many connection.
  4. 4
    A junction table, or associative entity, resolves the problem by connecting the original tables through two one-to-many relationships.
  5. 5
    To identify the pattern early, ask whether one record on each side can connect to multiple records on the opposite side.

Key Takeaways

  • Many-to-many relationships occur when records on both sides can connect to multiple records on the opposite side.
  • In an entity-relationship diagram, many indicators appear at both ends of a many-to-many connection.
  • A single foreign-key column cannot store all the related values needed for a many-to-many relationship.
  • A junction table resolves the relationship by creating two one-to-many relationships with foreign keys to both original tables.
  • Checking multiplicity from both directions helps identify the correct model before implementation.

Related Topics

Related concepts

More concepts starting with E