Associating large numbers of Items with large numbers of Users in Mongo Say I have tables Items and Users, which are both in the order of millions. Users can be associated with rows in Items via a 1 to many relationship. This is expected to be on average, a few thousand. What is the best way to go about this? I can only think of 2 ways to do it:


Create a separate table linking the 2 up. This could end up having billions of rows, so I have space concerns here.
Embed the item id's in each User, but then i'd be often pulling out more than I need, and inserting/deleting ID's would also be inefficient.


It seems like it would be a common scenario, so am wondering what kind of solutions are out there.
Consider two collections (or entities) users and items. And, they are in a one-to-many relationship - users is the 'one' side and the items is the 'many' side.

items: [ 1.. millions ]
  itemId: &lt;id value&gt;,
  name: &lt;some name&gt;,
  description: &lt;...&gt;,
  other_fields:  &lt;...&gt;


users: [ 1.. millions ]
  userId: &lt;id value&gt;,
  name: &lt;some name&gt;,
  email: &lt;some value&gt;,
  items: [ 1.. 10000 ]





  Embed the item id's in each User, but then i'd be often pulling out
  more than I need, and inserting/deleting ID's would also be
  inefficient.


This is a often used approach - embedding the items ids within the users document. This is done by storing the items ids within the users document as an array. For example:

items: [ itemId-1, itemId-2, ..., itemId-5000 ]


With this approach:


You can retrieve all items associated with a user.
For more details of specific item, lookup the items collection (this can be an aggregation lookup operation which queries both collections as a "join").
All the related information for a user is within one document.
For efficient querying, create indexes on the most used query filter fields for the users (e.g., userId, name) and the items array field.
Indexes on array fields are called as Multikey Indexes.


Another Approach:

You can also consider storing more information (as sub or embedded documents) about the items in the items array. For example,

items: [ { itemId: &lt;id-1&gt;, itemName: &lt;some name&gt; }, { itemId: &lt;id-2&gt;, itemName: &lt;some name 2&gt; }, ... ]


In this use case, the itemName is often used when you query a user and related items. This way you can avoid accessing the items collection for the item's name. This approach works when the field like itemName does not change and mostly stable (fields like item price and quantity can change often, and description might not change often).




  ... but then i'd be often pulling out more than I need, and
  inserting/deleting ID's would also be inefficient.


When you query for a user (users collection), you have the option to restrict the fields (user related and also items array) to get as result of a query. MongoDB's projection allows this restricting the fields to retrieve within a single document. This also applies to the items array field - only specific item(s), specific number of items, specific fields for an item, etc.

Regarding the inserting / deleting / updating operations - MongoDB has methods to update a single or multiple fields (including array fields) for a document as atomic operation. 

There are specific operators (referred as array operators) to work with arrays (with scalar or composite values) - for inserting, updating, deleting and querying (CRUD operations) on the array fields. Creating indexes on array fields will make these operations efficient.