I've been trying to figure out a solution to this problem and i'm looking for some general advice and best practice.
Imagine you have a webb app, perhaps a movie database like IMDB. You have a large amount of records (movies) that probably needs to stored in a search optimized data source like Elasticsearch.
Say we want our users to be able to store meta data related to individual movies. Comments, ratings, reviews etc. A non trivial amount of data. And we want users to be able to query the movies database and be presented with a list of matching movies along with user created meta data per movie in a single view, say a table.
Conceptually the easiest thing to do would be to store user created meta data along side movie data on a per record basis. However that approach seems, to me, unsustainable over time as number of users and user created meta data grows over time. Also user meta data is created on demand so you'd have to deal with constant updates and re-indexing and that seems costly from a performance and overhead point of view.
So then i'm left with a scenario where i'm fetching data from multiple data sources and i have to map and combine that data into a single dataset.
I'm wondering what approach i should take? Do i fetch all of dataset A, all of dataset B, map and combine and then send to client? Do i stream dataset A, query data source B per record and send to client?
I imagine this is a problem many people have faced before and i'm thankful for any guidance.