How to organise a many to many relationship in MongoDB

32.2k views Asked by At

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

There are 3 answers

6
Alexandru Petrescu On BEST ANSWER

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

1
Mamun Sardar On

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
Zameer Ansari On

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.