When dealing with two collections, lists and items, where each item can belong to multiple lists with a custom position in each list, the question arises: which approach would be more efficient without overcomplicating things?
A: Including an array of lists and their positions within each item document like so:
/* item schema */
{
_id: ObjectID, // itemID
lists: [
{
_id: ObjectID, // the listID
orderNr: Number, // position in that specific list
}
]
}
B: Creating an additional collection called contexts to store an array of itemIDs in the desired order for each list:
/* context schema */
{
_id: ObjectID,
listID: ObjectID,
items: [
{
_id: ObjectID, // itemID
orderNr: Number // position of item in the list
}
]
}
In my opinion, option B is preferable. By querying the list's context document using its _id field, you can retrieve a set of IDs from the items array and easily query those specific items directly via their _id fields.
On the other hand, if we consider scenario A with 5,000 items, each appearing in multiple lists with frequently changing positions, it could be quite taxing for Mongo to locate all these items based on values nested inside arrays within each item. In contrast, querying a limited number of items by their _id in option B seems more efficient as MongoDB can stop once the last matching item is found.
However, is there something about MongoDB's internal mechanisms that could make option A viable? It might require less maintenance but perhaps there's a third approach I haven't considered yet?