Subjects and lessons to explore.

Law

Databases & SQL · Foundations

Many-to-Many

9 min read 5 practice questions
7 key termsSourcesReport an error
Want it in plain words first? Jump to Eli explains — the same idea, no jargon.
On this page 9 sections
  1. In 30 seconds
  2. Why this matters
  3. The college version
  4. Eli explains
  5. Worked example
  6. Key takeaway
  7. Quick check
  8. Study tools
  9. Sources & references

In 30 seconds

In a , a row in one table can match many rows in another table, and the reverse is true too: a student can enroll in many courses, and a course can have many students. A single cannot represent that , because one value can only point at one row. Instead, a third table, the , holds the pairs. Each row stores one student ID and one course ID, recording exactly one at a time.

Why this matters

Many-to-many is where the relational model earns its keep. One-to-many covers simple cases like orders belonging to a customer, but real life is full of pairings that run both ways: students and courses, members and clubs, actors and films. Forcing those into one-to-many means duplicating data or losing information. The bridge table is the mechanism that captures both directions cleanly, and it explains why real systems keep an enrollment record for every student-course pairing. It also prepares you for joins, which combine tables, and for normalization, which shapes how data is split across them. It is the pattern that turns a collection of tables into a model of how the world actually works.

The college version

The working definition: both directions allow many

A many-to-many relationship exists when a row in table A can match many rows in table B, and a row in table B can match many rows in table A. Microsoft's Power BI documentation states it plainly: many-to-many relationships occur when a value in one table can relate to multiple values in another, and vice versa. Microsoft's EF Core documentation says the same thing in more formal terms: many-to-many relationships are used when any number of entities of one type is associated with any number of entities of the same or another type. The two halves matter equally: one side allowing many matches is not enough; the reverse must hold as well. That symmetry is exactly what separates many-to-many from one-to-many.

The classic example: students and courses

The canonical case is students and courses. A student enrolls in several courses over a term, and a course enrolls many students. Neither side is limited to one match: Maya might take Statistics, Web Design, and Spanish, while Statistics has thirty students. Microsoft's Power BI documentation names this exact scenario, students enrolled in multiple courses, as a common many-to-many case. The pairing is the enrollment: the fact that a particular student is in a particular course. Notice what the pairing is not. It is not a property of the student alone, and not a property of the course alone; it exists only as a connection between one row of each table. That is what makes it awkward to store.

The bridge table: a third table holds the pairs

The standard solution is a third table, often called a bridge table or . Microsoft's EF Core documentation describes the mechanism precisely: an additional entity type is needed to join the two sides of the relationship, known as the join entity type, which maps to a join table in a relational database. Each row contains a pair of identifying values, one pointing to a row on each side of the relationship. For students and courses, the bridge table might be called enrollments, and each row stores a student ID and a course ID. One row means one enrollment: Maya with Statistics is a different row from Maya with Web Design. Microsoft's Power BI documentation calls the same structure a bridging table that connects the two main tables by listing all valid combinations of their keys.

Why a bridge is needed: a foreign key alone cannot do it

A foreign key is a single value in one table that points at a single row in another table. W3Schools describes it as a column that refers to the primary key in another table, establishing a link between the two. That works for one-to-many: each order row carries one customer ID, and many orders can carry the same ID. But the pairing at the heart of many-to-many involves two rows at once, one student and one course, and no single column on either table can hold it. If the students table had one course-ID column, a student could take only one course. Several course-ID columns would leave empty slots everywhere and break as courses come and go. Microsoft's EF Core documentation says it directly: many-to-many relationships cannot be represented in a simple way using just a foreign key. The bridge table exists precisely because the foreign key alone runs out of room.

Reading the relationship in two directions

A many-to-many relationship reads naturally from either side, and the language shifts with the direction. From the student's side: Maya enrolls in Statistics, Web Design, and Spanish, so three rows in the enrollments table carry her student ID. From the course's side: Statistics has Maya, Diego, and twenty-eight others, so thirty rows in the enrollments table carry the Statistics course ID. The same bridge table serves both readings; you simply filter it by the column you care about. This two-directional reading is the practical payoff: questions like what courses does this student take or which students take this course are answered by the same table, because every pairing is stored once and can be found from either end.

Many-to-many versus one-to-many

The distinction is a single question: can the many run in both directions? In a , each row on the many side matches exactly one row on the one side: an order belongs to one customer even though a customer has many orders. Microsoft's Power BI documentation draws the contrast explicitly: in traditional one-to-many relationships, each value in one table matches only one value in another, but real-world data often breaks that rule. In many-to-many, both sides may match many, so a student has many courses and a course has many students. The storage difference follows directly. One-to-many fits comfortably with a single foreign key on the many side. Many-to-many needs the third table, because the pairing is a fact about two rows rather than about one.

The honest framing

Many-to-many is the least obvious relationship in the relational model, because it asks you to add a table that seems to contain no new information: the students table already knows the students, and the courses table already knows the courses. It is also the most powerful, because real systems are full of pairings that run both ways: students and courses, customers and accounts. Microsoft's Power BI documentation notes that many-to-many scenarios are common, citing customers with multiple accounts and students in multiple courses, and that modeling them accurately is exactly why the pattern exists. The bridge table looks like extra machinery until you realize it is the only honest way to record a two-sided fact.

Eli, the EliExplains learning guide

Eli explains

The same idea, in plain words

Explain it like I’m 10

Some pairings go both ways. A student takes many courses, and a course has many students. You cannot write that with a single foreign key, because a foreign key is one value pointing at one row. The trick is a middle table: the bridge table. Each row holds two IDs, one for the student and one for the course, and that row is one enrollment. To see Maya's courses, find every bridge row with her ID. To see who is in Statistics, find every bridge row with that course's ID. One table, two directions, every pairing stored once.

Picture it like this

Think of a school dance where students and teachers arrive separately. No student can carry a full list of teachers, and no teacher can carry a full list of students. So the door has a sign-in sheet: one line per pairing, Maya danced with Mr. Chen, Diego danced with Ms. Rivera. The sheet holds only pairs, one per line. Want to know who danced with Mr. Chen? Scan the sheet. Want to know who Maya danced with? Scan the sheet. The sheet is the bridge table; a line on it is a pairing.

Where the picture stops working

The sign-in sheet is only a list of names, but a real bridge table is a table with rules: every student ID must match a real student, every course ID must match a real course, and the database refuses pairings that break those rules. Also, a dance ends in one evening, while database pairings like enrollments persist and can be counted, queried, and changed. The sheet is a snapshot; the bridge table is a working record.

Worked example

Northwood Community College keeps two tables. The students table lists one row per student: Maya Okafor is S-104, Diego Ramos is S-105, and so on. The courses table lists one row per course: Statistics is C-207, Web Design is C-210. Enrollment is many-to-many: Maya takes three courses, and Statistics has thirty students. The registrar records this in a third table called enrollments. When Maya adds Statistics, the database inserts one row, (S-104, C-207). When Diego drops Web Design, it deletes the row (S-105, C-210), and nothing in the students or courses tables changes. To list Maya's courses, find every enrollments row carrying S-104 and read the course IDs. To list who is in Statistics, find every row carrying C-207 and read the student IDs. Each pairing exists exactly once, as its own row, and the two main tables stay clean.

Key takeaway

Many-to-many means the many runs in both directions, and that requires a bridge table: a third table whose rows each hold one identifying value from each side. Every pairing lives once, and both directions are readable from the same rows.

Quick check

3 questions here, of 5 in this lesson’s practice set. Answers stay hidden until you check.

Question 1 of 3foundational

How are two tables linked in a many-to-many relationship?

Choose an answer, then check it.
Question 2 of 3intermediate

A student enrolls in several courses, and each course has many students. What kind of relationship is this?

Choose an answer, then check it.
Question 3 of 3intermediate

A college database has a students table and a courses table. Where does the database record which student is in which course?

Choose an answer, then check it.
Practice all 5

Keep learning

Ready to build on this? Continue to the next lesson.

Practice this lesson
Study tools & related lessonsYou’ll learn to · Common mistakes · Easily confused · Key vocabulary · Related

You’ll learn to

  • Define a many-to-many relationship as one in which rows in both tables can match many rows on the other side.
  • Explain why a single foreign key cannot represent a many-to-many pairing.
  • Describe how a bridge table records pairings by storing one identifying value from each side per row.
  • Apply the students-and-courses pattern to read a many-to-many relationship in both directions.
  • Distinguish many-to-many from one-to-many by which side is allowed to have many matches.

Common mistakes

  • Thinking a foreign key alone can express a many-to-many relationship.

    A foreign key is one value pointing at one row. The pairing involves two rows, so it needs a row of its own in a bridge table.

  • Confusing many-to-many with one-to-many.

    Ask whether the many runs in both directions. If a course could have only one student, it would be one-to-many; because a course has many students and a student has many courses, it is many-to-many.

  • Squeezing several IDs into one column, or adding many ID columns.

    A single course-ID column on the students table caps a student at one course, and several columns break as courses come and go. The bridge table keeps each pairing in its own row.

  • Treating the bridge table as a copy of the other tables.

    The bridge table stores only identifying values, not names or descriptions. It is a list of pairings; the real facts stay in the two main tables.

Easily confused

Many-to-many relationship vs. One-to-many relationship

In one-to-many, only one side allows many matches; in many-to-many, both sides do. One-to-many fits a single foreign key on the many side, while many-to-many needs a bridge table.

Bridge table vs. Main table

A main table holds facts about its own subject, such as a student's name or a course's title; a bridge table holds pairings between two other tables and contains no facts about either side beyond the identifying values.

Pairing vs. Single foreign-key reference

A foreign-key reference links one row to one other row; a pairing links one row of one table to one row of another as a record of its own, which is what a bridge table stores.

Key vocabulary

many-to-many relationship
A relationship in which a row in either table can match many rows in the other table; the many runs in both directions.
bridge table
A third table that records a many-to-many relationship by storing one row per pairing, each row holding one identifying value from each side.
join table
Another name for a bridge table, used in database documentation for the table that connects two sides of a many-to-many relationship.
pairing
The connection between one specific row in one table and one specific row in another, such as one student with one course.
foreign key
A column in one table whose values reference the primary key of another table, linking rows across the two tables.
one-to-many relationship
A relationship in which a row in one table can match many rows in another, but each of those rows matches only one row back.
enrollment
A single student-course pairing recorded as one row in a bridge table.

Sources & references

  1. Many-to-many relationships - EF Core — Microsoft Learn
  2. Many-to-many relationships in Power BI Desktop — Microsoft Learn
  3. SQL FOREIGN KEY Constraint — W3Schools

EliExplains lessons are original prose written from the open, credible references above. See Copyright & Licensing.

Researched 2026-08-21

Educational content only. It is not medical, legal or professional advice. Found an error? Tell us.