I have a specific problem with many to many relationship implementations in MongoDB.
I have collections of Songs and Artists(Millions document). Here the song can be sung by Many Artists and an artist can sing Many songs. So I followed the approach of Document referencing in both collections. like this...
1. Songs Collection:-
{
_id:ObjectId("dge547567hheheasfw3454dfg"),
title:"xyz",
artists:[ObjectId("xfvdg464654"), ...] //many artists // artists ids
}
2. Artist Collection:-
{
_id:ObjectId("dge547567hheheasfw3454dfg"),
title:"xyz",
songs:[ObjectId("xfvdg464654"), ...] //many songs // songs Ids
}
But here the problem is while deleting artist I have to delete an artist from an array of the artist in all document of the song which has an artist and vice versa. Which can cause the problem of Atomicity. How can I ensure atomicity here?
Secondly when the database will grow and songs are sung by the artist will increase thus resulting document growth of both collection and document size can reach to 16MB or greater(MAX DOC SIZE).
So what can be done here in this case?
A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.
In general, we recommend limiting collections to 10,000 per replica set. When users begin exceeding 10,000 collections, they typically see decreases in performance. To avoid this anti-pattern, examine your database and remove unnecessary collections.
In MongoDB, a relationship represents how different types of documents are logically related to each other. Relationships like one-to-one, one-to-many, etc., can be represented by using two different models: Embedded document model. Reference model.
Performance Scale After evaluating multiple technology options, AHL used MongoDB to replace its relational and specialised 'tick' databases. MongoDB supports 250 million ticks per second, at 40x lower cost than the legacy technologies it replaced.
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"),
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With