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 ValueAn Ingredient has a collection of NutrientsA Recipe has a Collection of Ingredients and occasionally can have a collection of other recipesA Meal has a Collection of Recipes and IngredientsA Menu has a Collection of MealsThe relations can be depicted asIn 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.
Nested Entities and calculation on leaf entity property - SQL or NoSQL approach
c#;sql;database design;nosql;graph databases
Based on the requirements and  architecture, there may be performance improvement options:You may use indexed views(matrialized) To improve readperformance on RDBMS(Sql server) level. Basically, all youneed to do is: Create a regular view. Create a clustered indexon that view.Using a cashing mechanism in application level will improveperformance. If its possible and feasible to use cashing, havinga cash strategy like singleton lazy cashing will help you.NoSql:There are lots of good articles about Sql vs NoSql, like this and thisThe parts interests me : Where to use NoSql:If your DB is 3NF and you dont do any joins (youre just selecting a  bunch of tables and putting all the objects together, AKA what most  people do in a web app.When used be ready to: You end up writing jobs to do things like joining data from different    tables/collections, something that an RDBMS would do for you automatically.Your query capabilities with NoSQL are drastically crippled. MongoDb may be the closest thing to SQL but it is still extremely far behind. Trust me. SQL queries are super intuitive, flexible and powerful. NoSql queries are not.MongoDb queries can retrieve data from only one collection and take    advantage of only one index. And MongoDb is probably one of the most flexible NoSQL databases. In many scenarios, this means more round-trips to the server to find related records. And then you start  de-normalizing data - which means background jobs.The fact that it is not a relational database means that you won't    have (thought by some to be bad performing) foreign key constrains to  ensure that your data is consistent. I assure you this is eventually is going to create data inconsistencies in your database. Be prepared. Most likely you will start writing processes or checks to keep your database consistent, which will probably not perform better than letting the RDBMS do it for you.Forget about mature frameworks like hibernate.Beside deciding to use or not to use NoSql, a helpful article on NOSQL DBMS Comparison and the intention of them could be found here as some of them are focused on high reads, low writes, map-reduce, HA ... Having a look at the ranking and popularity of them, by category may be usefull.