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.

Diplopia

Did it resolve you query ? Please see [What should I do when someone answers my question?](stackoverflow.com/help/someone-answers – Fleur

Answer 1

This question is old, but a practical example would be found in social networks like Instagram:

You (the follower) follow a person A (the followee). You also follow person B, person C, etc..., but you are not the only one who may follow person A, as well as not the only one who may follow person B, person C, etc... Your friend or other people may as well follow them too.

So you end up with data shaped in the following way:

     Follower | Followee
--------------|--------------
          ... | ...
          You | A
          You | B
          You | C
  Your friend | A
  Your friend | B
  Your friend | C
          ... | ...

Which is what you call a bridging table (aka lookup table), describing a many-to-many relationship.

Continuing with the social network example, you need a many-to-many bridging/lookup table otherwise you would have to introduce redundancy in your users table, because you would need to duplicate your You record and that of your friend (Your friend) for each of your followees (A, B, C), which is of course non-practical and violates normalization.

do you need a bridging table for a one to many relationships as well ? As far as I understand you don't need a bridging table for a one to many relationship, but a friend of mine recently told me otherwise.

You may use a bridging/lookup table for a one-to-many relationship for flexibility purposes when e.g. you don't know in advance if the relationship of your data is effectively many-to-many or the relationship is one-to-many but you think that it can evolve and become many-to-many in the future.

Pallet

Good example. The only reason I voted another example up is because social networks are implemented in graph databases these days. – Objectionable

Answer 2

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:

Fleur

Answer 3

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.

Shumaker

Answer 4

Many-to-many relationships are quite common in the real world. For instance, a reader can read many newspapers, and a newspaper can have many readers.

Normalized version

Newspaper           Reader                 Subscription          
---------           ------                 ------------
id                  id                     id
name                name                   newspaper_id
                    address                reader_id

Here the Subscription table is what we generally call a 'join' table.

We should understand the need for this table.

Let's say A is reader of N1 and N2 newspapers. B is a reader of N2 and N3 newspapers.

For the above schema the tables would be as following.

Newspaper           Reader               Subscription
---------           ------               ------------
1 N1                1 A xyz              1 1 1
2 N2                2 B abc              2 1 2
3 N3                                     3 2 2
                                         4 2 3

Queries

Query 1: find the names of the newspapers read by A

Query 2: find the names of readers who read N2

Query 3: add a newspaper N3 to reader A

Denormalized schema

Let's say there was no table Subscription. Then I would have to add records of readers in my Newspaper table and newspapers in my Reader table. The denormalized schema would be as follows.

Newspaper           Reader                           
---------           ------                 
id                  id
nid                 rid                     
name                name
reader_name         address
                    newspaper_name

The tables would look like the following:

Newspaper           Reader                           
---------           ------                 

1 1 N1 A            1 1  A  xyz N1                           
2 2 N2 A            2 1  A  xyz N2 
3 2 N2 B            3 2  B  abc N2
4 3 N3 B            4 2  B  abc N3

Information duplication

As you can see there is duplication in storage here. When we have to add a new subscription for user A for N3 (query 3), we would need to insert rows both in Reader and Newspaper tables while in the earlier schema there would only be a single new row added in the Subscription table.

Compute vs IO

But in the read path you have to do compute intensive joins. In query 1, you will have to get the id of A from Reader table, join the tables Subscription and Newspaper to get the newspaper names read by A. If you were to do query 1/2 in the denormalized version, you will have to only read 1 table.

            Type           Normalized                  Denormalized
Query 1     Read             3 reads                    1 read

Query 2     Read             3 reads                    1 read

Query 3     Write      2 reads (reading of id)     2 reads (for reading id)
                        1 write                       2 writes

It would take a careful analysis of your current usage pattern requirements and future evolution to take the call between the two.

If you were to use a RDBMS like MySQL, and would like to optimize storage, you would go for the normalized schema. If you were to use a NoSQL like Cassandra, you would probably go for the denormalized schema. In the age of cloud where storage is cheaper than compute (probably because of slowing down of Moore's law), often the denormalized schema makes more business sense when your scale of data becomes huge.

Counterpane