How to handle Many to Many relationship in mongoDB?

Let's start with detailing our Many-to-Many relationship for your case and try to understand what can be and cannot be done -

• A song can be sung by as many as 10 or maybe 20 artists (assuming it isn't as complex/diverse to require maybe 100's of artists).

  In this case, bucketing artist's id inside of songs collection is perfectly fine and we can safely assume that even in the worst case scenario (storing a complex/diverse song sung by 100 artist) it will never force our song collection beyond 16 MBs.

• An artist, however may very well sing upto 1000s of songs or maybe more in his enitre career. An ObjectId being 12 bytes long, in this case will grow a collection to a size of merely 12000 bytes which is way lesser than 16000000 bytes. You are still left with a lot of space. So no need to worry about hitting the cap of 16MB.

Approach - 1

Inter-bucketing works really well for relations expecting high reads.

Songs for some artists can be fetched in single query and even vice versa. And this would be even smoother with indexes sprinkled over these two collections.

But if we go around bucketing artists inside of songs and songs inside of artists then our updates are no more Atomic but for that we can still implement an application level Two-phase commit for both artists and songs CRUD, which even after being a little troublesome, solves the problem.

Approach - 2:

Why not bucket only artist id's inside of songs collection and have multikey index on that field.

List of artists who sang a song is way too short than the list of songs sung by an artist. So we only bucket artists inside of songs collection.

This way we will -

1. avoid the near to impossible possibility of hitting maximum size of artist's collection if we had bucketed songs inside of artists collection.

2. avoid writing 2P commits for atleast songs collections. All relational reads can be satisfied via songs collection only (here i am excluding the _id lookup for artist)

3. ensure fast data access in just a single query even when reverse querying on song collection for songs sung by an artist.

You will already be having some info(_id) of artist for which you need to fetch songs. You just draft a query like this -

 db.songs.find({ artists: 'your-artist-id' });

And when you explain this query, you find happiness when you realize that it utilizes your multi-key indexes. Great job there !

Now which approach to go for ?

I find the second approach a little more subtle for your use case as it reduces some of the complexity of managing 2P commits for atomicity and still provides a good read performance. First approach definitely is reads oriented so if you're sure that you will be recieving a lots and lots of reads on both the collection, go for first one otherwise second one should do the trick.

I implemented many to many relationship in mongodb by taking third collection similar to what we do in sql.

Song Collection

{
  _id:ObjectId("dge547567hheheasfw3454df12"),
   title:"xyz",
   length : 123
}

Artist Collection

{
   _id:ObjectId("dge547567hheheasfw3454d32"),
   name:"abc",
}

SongArtist Collection

{
   _id:ObjectId("dge547567hheheasdfsdfsdfgdfga42"),
   artist: ObjectId("dge547567hheheasfw3454dfg32"),
   song: ObjectId("dge547567hheheasfw3454df12"),
}

• Now when you do crud operations and if you want to delete the artist from a song you can do it in single query in SongArtist Collection.
• It will never have any problem for exceeding document size
• If you want to delete particular artist in particular song you have to query once
• It will increase the number of records in a collection but mongodb can handle that very easily.
• You can find all the song related to one artist in single query and vice versa.