I am looking for a way of dealing with the following situation:
1- Have an items collection in MongoDB
2- Have a users collection in DynamoDB
3- Each document in the items collection has a "reference" to a user or multiple users via an id attribute of the DynamoDB.
4- I need to query the items collection and populate each item with metadata from the user table (In each item, I have to replace the id reference/s with user metadata)
5- The DynamoDB has to remain as the main source of truth for the users, and can't move the MongoDB data over.
I'm wondering what would be the best solution for this.
At the moment I'm thinking of two options:
One is getting the items from MongoDB, getting the users ids, query all the users from DynamoDB and inserting the user's data in the items. As the items are somewhat a complex collection, there is a lot of potential to introduce new bugs and making it hard to maintain in the future.
Second one is copying the DynamoDB users to MongoDB and querying directly in MongoDB. The issue here is that the collection has to be kept in sync all the time and I'm not convinced about having the same data in different places due to the increased complexity.
As no approach is 100% perfect, just wondering if there could be a better way to do this.
Which option (or other) would be the way to go?