Many to Many Relationships in Mongo

Hey, I've got a bit of a problem right now trying to figure out how to resolve a specific many to many model in Mongo.

I have an event scheduling system for the CRM I am building that allows events to be assigned to both users and teams. These events are particular to each lead.

So for example, I have a call at 5:00pm Thursday with Jimmy Dolittle. My sales team also has a call Thursday at 7:00am with Bob Jones.

If this were SQL, I would just create a leads table, events table, users table, and teams table. I was thinking about putting the events in the users collection and in the teams collection but then the problem arises when I have a list of leads and want to display 开发者_运维技巧the callback date next to each lead. Referencing like that in Mongo is going to be sloooow with a list of 500 leads.

I was also thinking about storing the events in the leads collection, but that would mean I would have to do the same sort of search for leads with events assigned to a particular user or team (there might be 500,000 leads in the database but only 500 have events for a particular user.

This kind of relationship is just going to be a problem in Mongo. In that situation, I would probably write a function for connecting those objects at the application level. Whenever a connection is made, save the relationship in both objects. Then you can search either direction with ease. You'll have redundant data and that causes a risk of getting them out of sync, but that's the price you have to pay with a non-relational structure. Your updates won't be as fast since you'll have to update two docs, but your selects should be speedy.

As Tim Suggests a good idea would be to solve this at the app level.

What i'd do here is create a new collection 'Events' then store an array of _id's of related events inside the user and team objects from here it will be super fast to do a look up. It may mean a lot more queries but queries on the _id field alone are highly optimised and not very resource intensive (unless you have millions of events per user) so if a team has the app up they can see their events and if a user has theirs up they can see their events.

Also i recommend storing back links to the user and team _id's in the event object. Yes this is redundant data but its only a reference and if managed properly at the app level should keep the schema nice and tidy.

Best of Luck.