MongoDB: Replicate data in documents vs. “join”
- by JavierCane
Disclaimer: This is a question derived from this one.
What do you think about the following example of use case?
I have a table containing orders.
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)
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).
I'll have to replicate all the products' related data? (ie: category, maker and optional attributes like color, size…)
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)
Is it fair enough to apply the $unwind operation to my orders original collection? (What about the performance?)
I should have to do another collection with these queries in mind and replicate again all the products' information (and their orders)?
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?)
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 )
