MongoDB: Replicate data in documents vs. “join”

Question

Disclaimer: This is a question derived from this one.

What do you think about the following example of use case?

1. I have a table containing orders.
2. These orders has a lot of related information needed by my current queries (think about the products; the buyer information; the region, country and state of the sale point; and so on)
3. In order to think with a de-normalized approach, I don't have to put identifiers of these related items in my main orders collection. Instead, I have to repeat all the information for each order (ie: I will repeat the buyer's name, surname, etc. for each of its orders).

Assuming the previous premise, I'm committing to maintain all the data related to an order without a lot of updates (because if I modify the buyer's name, I'll have to iterate through all orders updating the ones made by the same buyer, and as MongoDB blocks at a document level on updates, I would be blocking the entire order at the update moment).

This raises the following questions:

1. Do I have to replicate all the products' related data? (ie: category, maker and optional attributes like color, size…)
2. What if a new feature is requested and I've to make a lot of queries with the products "as the entry point of the query"? (ie: reports showing the products' sales performance grouping by region, country, or whatever)
   1. Is it fair enough to apply the $unwind operation to my orders original collection? (What about the performance?)
   2. I should have to do another collection with these queries in mind and replicate again all the products' information (and their orders)?
   3. Wouldn't be better to store a product_id in the original orders collection in order to be more tolerable to requirements change? (What about emulating JOINs?)
3. The optimal approach would be a mixed solution with a RDBMS system like MySQL in order to retrieve the complete data?
   • I mean: store products, users, and location identifiers in the orders collection and have queries in MySQL like getAllUsersDataByIds in which I would perform a SELECT * FROM users WHERE user_id IN ( :identifiers_retrieved_from_the_mongodb_query )

Answer 1

In order to get join-like information from your model, (like the statistics on products), you can also do one of these two things:

• createa a separate products_statistic collection which is updated with the needed info each time an order is saved/updated in the db
• create a map-reduce operation that calculates the needed info for products (takes in orders, for each product in each order counts statistics x,y,z for that product). Mongo's integration with Hadoop can greatly increase performance in this case (check it out here)