Nested Entities and calculation on leaf entity property - SQL or NoSQL approach
- by Chandu
I am working on a hobby project called Menu/Recipe Management.
This is how my entities and their relations look like.
A Nutrient has properties Code and Value
An Ingredient has a collection of Nutrients
A Recipe has a Collection of Ingredients and occasionally can have a collection of other recipes
A Meal has a Collection of Recipes and Ingredients
A Menu has a Collection of Meals
The relations can be depicted as
In one of the pages, for a selected menu I need to display the effective nutrients information calculated based on its constituents (Meals, Recipes, Ingredients and the corresponding nutrients).
As of now am using SQL Server to store the data and I am navigating the chain from my C# code, starting from each meal of the menu and then aggregating the nutrient values.
I think this is not an efficient way as this calculation is being done every time the page is requested and the constituents change occasionally.
I was thinking about a having a background service that maintains a table called MenuNutrients ({MenuId, NutrientId, Value}) and will populate/update this table with the effective nutrients when any of the component (Meal, Recipe, Ingredient) changes.
I feel that a GraphDB would be a good fit for this requirement, but my exposure to NoSQL is limited.
I want to know what are the alternative solutions/approaches to this requirement of displaying the nutrients of a given menu.
Hope my description of the scenario is clear.
