How to organise a many to many relationship in MongoDB

Question

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

Theola

See also answers to this question and this question – Conflagration

I know this is rather old but I am wondering about scale as well. What if you have 1000 groups? – Hateful

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) – Hornstone

Answer 1

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

Acima

Excellent thanks for pointing me to the docs too. Making sense now, it is a different approach and more flexible and performant it's exciting to get into this stuff. Also mongodb.org/display/DOCS/Schema+Design – Theola

Just seen Kyle Banker answer my question at 29 minutes into this presentation video : blip.tv/file/3704083. Never use a join table to represent a many-to-many, instead have a list of ObjectIds in both collections – Theola

How does this scale though? Having a list of users in the group document could get out of control quickly when you have potentially thousands of users right? – Pharyngeal

What about consistency? If you delete one side, but something prevents you from deleting the other side, how do you check this? – Magritte

I think -- when using -- a document store you should not put so much weight on data integrity as such a solution is not meant to optimise that, rather data availability. Software Engineering is a world of trade-offs. If data integrity is really important you'd probably should go for a relational solution. At my company we discard data (read: id's) which we cannot populate and do not show the user an error. Which you simply can't do in any relational solution as the database layer will throw an error. We have tools in place to cleanup the datastore, which are ran by a profiler. – Cinereous

Could someone explain please why we need the cross reference? I got it, that this will help use to get another side objects list right from the document we received from the first query, but why can't we just use reverse referencing with proper indexes? I can't see a real benefit from having another reference when it could be done without it. I might miss some important thing here, please help me to figure it out. – Shoplifter

Answer 2

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.

Greyhen

damn, I was looking for something like this to finally understand how to think when modeling a database – Imperfection

Answer 3

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.

Injudicious

Using multiple collections to perform joins is discouraged in MongoDB. its better to store an array of reference ID's in both the collections – Bitartrate