What is the best way of writing a collection schema to map another collection?

• Assumptions:

  • There were one collection before and you split them into multiple collections as they are getting large and you want to solve it without sharing.

• I would not even create a collection for the following data. This data is static reference data and will act as a router. On start up, application loads this data and creates a router.

    {
      "type" : "Fashion",
      "gender" : "Gents",
      "kids" : "true",
      "collection" : "boys_fashion"
    }
    {
      "type" : "Accessories",
      "gender" : "Ladies",
      "kids" : "false",
      "collection" : "ladies_accessories"
    }
    ...

• What do I mean by creating a kind of router by that static configuration file? Lets say you receive a query fashionable items for baby girls. This router will tell you, hey you need to search girls_accessories collection. And you send the query to girls_accessories collection and return the result.

• Lets take another example, you receive a query for fashionable items for female. This router will tell you hey you need to search, ladies_accessories and girls_accessories. You send the query to both collections and combine the result and send it back.

Conclusion

If my assumptions are correct, you don't need a collection to store the reference data. What you have done is manual sharding by splitting the data across different collections, the reference data will act as router to tell you where to search and combine

Update based on comments

• Query does not involve multiple collections
• Administrator can add new collection and application should query it without modifying code.

Solution 1

• You create a collection for the reference data too
• Downside to this is that every query involves two calls to database. First to fetch the static data and second to the data collection

Solution 2

• You create a collection for the reference data too
• You also build a Dao on top of that which uses @Cacheable for the method that return this data.
• You also add a method in Dao to clear the cache with @CacheEvict and have a rest endpoint like /refresh-static-data that will call this method`
• Downside to this method is that whenever administrator add new collection, you need to call the endpoint.

Solution 3

• Same as solution 2 but instead of having an endpoint to clear the cache, you combine it with scheduler

    @Scheduled(fixedRate = ONE_DAY)
    @CacheEvict(value = { CACHE_NAME })
    public void clearCache() {      
    }

• Downside to this solution is that you have to come up with a period for fixedRate which is acceptable to your business

I added the collection like below.

 /* 1 */
    {
        "type" : "fashion",
        "category" : [ 
            {
                "value" : "gents",
                "true" : "boys_fashion",
                "false" : "gents_fasion"
            }
            
        ]
    }
    
    /* 2 */
    {
        "type" : "accessories",
        "category" : [ 
            {
                "value" : "ladies",
                "true" : "girls_accessories",
                "false" : "ladies_accessories"
            } 
           
        ]
    }

and will fetch the data using the below query

findOne({"type":type,"category.value":cvalue},{"_id": 0, "category": {"$elemMatch": {"value": cvalue}}})

Try to make subdocuments in MongoDB, instead of nested objects https://mongoosejs.com/docs/subdocs.html