Can somebody give a practical example of a many to many relationship?

Question

I learned about many-to-many relationships in College, and I never really understood them. So far I've been working with one-to-many relationships which are easy to understand and deal with.

Can somebody please give a practical example of a many-to-many relationship, and explain why we need a bridging table for it. Plus, do you need a bridging table for a one-to-many relationship as well? As far as I understand you don't need a bridging table for it, but a friend of mine recently told me otherwise.

Answer 1

One-to-many & many-to-many relationships are not the property of the data rather the relationship itself. And yes you do need bridging/third table for many-to-many relationship in perfect normalized RDBMS world. Lets see each of it with real life example:

One-to-many relationship: When one row of table A can be linked to one or more rows of table B.

Real Life Example: A car manufacturer makes multiple models of the cars, but a same car model can't be manufactured by two manufacturers.

Data Model:

Many-to-many relationship: When one row of table A can be linked to one or more rows of table B, and vice-versa.

Real Life Example: A user can belong to multiple community, and a community can have multiple users.

Data Model:

Answer 2

A practical example to many-to-many is the courses taken in a college. You might have a Courses table like:

CourseId | CourseName
=====================
   1     | Algebra 101
   2     | Intro to Calculus
   3     | Advanced C++
   4     | Go Programming
...

And there are Students:

StudentId | Name
===========================
   1      | John Doe
   2      | Frank Smith
   3      | Mary Brown 
...

Now, if you think of it, a Student can take multiple (many) Courses and a Course can have many attendant Students. That constitutes a Students(many)-to-(many)Courses relation. There is no way to directly express this without a bridge table (I am lying here but accept there is not), so you create intermediate 3rd table:

Students_Courses

StudentID | CourseID
====================
    1     |    1
    1     |    3
    2     |    2
    2     |    4
    2     |    1  
    3     |    2
    3     |    4

We are saying:

John Doe (1) is taking (Algebra 101 and Advanced C++), 
Frank Smith (2) is taking (Algebra 101, Intro to Calculus and Go Programming)
Mary Brown (3) is taking (Intro to Calculus and Go Programming)

This is like 1-To-Many looking from Students' perspective. We can also look from Courses' perspective:

Algebra 101 members are (John Doe and Frank Smith)
Intro to Calculus members (Frank Smith and Mary Brown)
Advance C++ members (John Doe)
Go Programming (Frank Smith and Mary Brown)

making another 1-To-Many from the other side.

IOW it looks like:

Student +-< Courses and Students >-+ Course
            Courses >-< Students

A Many-to-Many bridging table doesn't need to only have IDs from two tables. It is what you need at least but may have other columns if you need like:

StudentId | CourseID | RegistrationDate | CompletionScore
=========================================================
   1      |     1    |  2017/02/15      |      A+
   1      |     3    |  2017/04/07      |  NULL

And 1-To-Many tables DO NOT have a bridging table. A typical example is Customers and Orders. A Customer can have (many) Orders but an Order belongs to a single (one) Customer. Orders table itself directly has a foreign key (CustomerId) pointing to its belonging Customer so there is no bridge table.

Note: These are in context of the traditional RDBMS. A many-to-many might be expressed without a bridging table but at this point I would assume that as advanced topic.