Change Id to be assigned from group is certainly a way to fix it, but one thing popup in my mind is the performance. This map and reduce index involve on 3 different collections. The key of this index is amount.

First alternative

If we only join credits and charges collection without worry about users, then the map and reduce index only have to worry about user Id and amount. That way, we won't have such error from happening and to load it, the effort is minimum as well.

Since user often have many other fields, such as phone, email etc. In the business logic, might be best to lazy load user and lazy fetch user's amount from index. First it make map and reduce index simple and any property of user change won't cause map and reduce index to trigger.

I'm not sure how well RavenDB handles property that not involved within the index, if user's email changed, I will assume map and reduce index will trigger, for any data that associated with that user.

// Charges + Credits map and reduce index
from c in docs.Charges
select new  {
    Id = c.User,
    Amount = -c.Amount
}

from c in docs.Credits
select new  {
    Id = c.User,
    c.Amount
}

// reduce

from r in results
group r by r.Id into g
let user = g.FirstOrDefault(x=>x.Name != null)
select new 
{
    Id = g.Key,
    Amount = g.Sum(x=>x.Amount)
}

Second alternative

Instead have map and reduce on 2 or 3 collections, let's have 2 map and reduce index on each collection. That way, when new credit or charge been added or modified, only one index will trigger. Since we have lazy operation in RavenDB client, retrieve shouldn't be an issue. Unless we want to order it.

Third alternative

Same as second approach to have two map and reduce index on charges and credits, we could summarize user's amount in user each time credit or charge been created or updated. That will require dedication to make sure it is always updating it. If any time unsuccessful update, it might require to go through all users to summarize again.

Then, similar to manually summarize on each modification, I remember Oren had an blog post about save index result in another virtual collection, and index that virtual collection instead. Oren can probably answer this, are we able to store outcome of two map and reduce index, then have another merge index to collect outcome? Such as.

• Credits map and reduce index => store in virtual collection
• Charges map and reduce index => store in virtual collection
• Merge index based on first and second virtual collection

The benefit to have charge and credit individual map and reduce index, is that we can use it to summarize who had most credits and who had most charges. Of course in exist map and reduce we can also do that, by have three fields. amount, charges and credits.

The only benefit of join three collection is to have more info from user's collection for full text search or filter purpose.

So, Oren, from speed, memory usage and amount of operation cost point of view, what would be better approach on such scenario? Of course we still need to consider actual business requirement.

Oren:

Sorry, my question seems not well described. When I was going through the scenario earlier, I was thinking of joined index and cost of any entity been changed. For index only involve with single collection, that's probably the simplest. Of course there are still details on how index outcome been maintained and how database update index outcome.

If we just focus on index that associated with multiple collections. We have few variants.

One to many joined index

In the scenario of this blog, it is credits or charges.

from c in docs.Charges
let user = load<User>(c.User)
select {
    name = user.name,
    email = user.email,
    amount = c.amount
}

For such index, any time a charge been added, modified or delete. The cost is minimum. Where, when a user has been modified, the cost can be huge. Depends on how many charges associated with the given user.

One thing I am not certain is, if a given user's phone has been changed, will that cause above index to go through all charges that associated with the given user, even though user's name and email has not been changed?

Multiple collection map and reduce

For such index, as what you have in your blog. What kind of entity change will cost most resource for such index?

• New user
• user modified
• user deleted
• charge added
• charge modified
• charge deleted
• credit added
• credit modified
• credit deleted

If the index is smart enough, I think it could figure out the user document ID, and modify existing index outcome, without go through all the charges and credits that associated with that user. As business logic developer, it would be good to understand of the cost of each index, then to put them into consideration on any new feature or improvement for existing logic.

Once we have more insight of the cost. For cases such as credit or charge deletion will cost most resource, as an example. Then when we have business rule says we will never delete credit or charges, then such index won't be costly at all.

Cool, Thanks Oren. Multi map index is something I haven't utilize as much, thanks for clarify that.