I’m working on a domain in which documents in a few collections (say 4) can have many-to-many relationships with documents in many other collections (say 40). I won’t go into the details of this domain as it’s quite specialised. But to illustrate the problem, you can think about visits to locations.
For example, it’s Saturday and I’ve got a lot on! In the morning, I leave home to go to my doctor to get a flu shot. Then I go to the local mall where I visit a few shops, including a restaurant where I have lunch. Next, I go to the beach for the afternoon, followed by dinner at a friend’s place. Then in the evening, I go to the movies before returning home.
So there are many different types of location that I could visit (say 40), and in terms of modelling, each type needs its own separate collection because of its specialised data. But it’s easy for each location to have a to-many relationship to all the people that visit it, and to answer questions like how many people visited this doctor or that restaurant on Saturday.
But for me, how do I get a list of all the locations I visited on Saturday? I also need a to-many relationships to each type of location (say, to many shops or friends’ homes). So with 40 types of location, that’s 40 different many-to-many relationships. Can I list the locations I visited on Saturday without issuing 40 different queries and then aggregating them?
And in-person visits aren’t the only option, as I could also visit the websites of most of those locations, or else I could phone them. So I would have three separate collections for in-person and website visits and phone calls, but in each case I need to answer the same questions.
I realise this problem is more suited to a graph database. But I have other needs (such as multi-field unique constraints and change streams) which graph DBs can’t meet.