How to organise a many to many relationship in MongoDB

63
votes

I have two tables/collections; Users and Groups. A user can be a member of any number of groups and a user can also be an owner of any number of groups. In a relational database I'd probably have a third table called UserGroups with a UserID column, a GroupID column and an IsOwner column.

I'm using MongoDB and I'm sure there is a different approach for this kind of relationship in a document database. Should I embed the list of groups and groups-as-owner inside the Users table as two arrays of ObjectIDs? Should I also store the list of members and owners in the Groups table as two arrays, effectively mirroring the relationship causing a duplication of relationship information?

Or is a bridging UserGroups table a legitimate concept in document databases for many to many relationships?

Thanks

3
mongodbmany-to-manyrelational-databasedocument-database
See also answers to this question and this question – Matthew Murdoch
I know this is rather old but I am wondering about scale as well. What if you have 1000 groups? – FarscapePROJ
Great point-- Another option, in this case, is to use the equivalent of a junction relation from a SQL database- an intermediate collection with two foreign keys- one for each related collection. In this case, you can execute 3 queries: (1) a normal find() to get the parent results, (2) an IN query to get the intermediate results, and finally (3) an IN query using the foreign keys in the intermediate results to find child records. (This is how we implement this feature in Waterline) – mikermcneil

3 Answers

36
votes

What I've seen done, and what I currently use are embedded arrays with node id's in each document.

So document user1 has property groups: [id1,id2]

And document group1 has property users: [user1]. Document group2 also has property users: [user1].

This way you get a Group object and easily select all related users, and the same for the User.

This takes a bit more work when creating and updating the object. When you say 2 objects are related, you have to update both objects.

There's also a concept DBReferences in MongoDB and depending on your driver, it'll pull referenced objects automatically when retrieving a document.

http://www.mongodb.org/display/DOCS/Database+References#DatabaseReferences-DBRef

10
votes

In-case anyone interested, I just bumped into a very good article posted in mongoDB blog. 6 Rules of Thumb for MongoDB Schema Design. There are 3 parts in this article, after reading all 3 you'll have a good understanding.

1
votes

Let's understand Many to Many Relations with an examples

  • books to authors
  • students to teachers

The books to authors is a few to few relationship, so we can have either an array of books or authors inside another's document. Same goes for students to teachers. We could also embed at the risk of duplication. However this will required that each student has a teacher in the system before insertion and vice versa. The application logic may always not allow it. In other words, the parent object must exist for the child object to exist.

But when you have many to many relationship, use two collections and have a true linking.