I learned in the amazing Mongo University Data Modeling course that typically a many-to-many relationship is modeled as two collections, with an array of references to one collection in the other collection.
I’m facing a design problem, and I would like to get the opinion of some people with more experience than me. I need to make a good argument for my plan (laid out below) because someone else working on this project is suggesting a lookup table, essentially. He comes from a SQL background - so I feel in some ways that it’s one of those “if you’re a hammer, everything looks like a nail” scenarios. But I may also be wrong!
Consider two collections: one that stores Users and one that stores Clubs. A Club may have many Users (and must have at least one), and a User may belong to many Clubs.
Because a Club can assign permissions to its Users, and within the scope of the Club, those permissions will need to be easily queryable, I decided to store User references in Club documents.
However, it is also crucial that all of a User’s Clubs are easily queried. So after some consideration, I decided to take the hit on data redundancy and also store an array of Club references in User documents. Now, when I need all of a User’s Clubs, I query on the User ‘Clubs’ field, and when I need all of a Club’s Users, I query on the Club’s ‘Users’ field. From my (inexperienced) point of view, I’m trading the cost of each User’s array of Club _id’s (which I think are 8B/ObjectId) for improved querying.
I guess my partner wants to store { UserId, ClubId, Permissions } documents in a new join collection. It just feels icky and too “SQL-y” to me. But otherwise, I can’t think of a really good reason not to do it this way, other than the fact that now I’ve got to make two queries or a $lookup for either mentioned query - every time. I know that if I ever choose to shard either collection, that I won’t be able to use it as the joined collection in a lookup - so I’m weary to go that route.
Thanks for any help.