Nested Entities and calculation on leaf entity property – SQL or NoSQL approach

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.

Based on the requirements and architecture, there may be performance improvement options:

You may use indexed views(matrialized) To improve read
performance on RDBMS(Sql server) level.
Basically, all you
need to do is:
Create a regular view.
Create a clustered index
on that view.
Using a cashing mechanism in application level will improve
performance.
If its possible and feasible to use cashing, having
a cash strategy like singleton lazy cashing will help you.

NoSql:
There are lots of good articles about Sql vs NoSql, like this and this

The parts interests me :

Where to use NoSql:

If your DB is 3NF and you don’t do any joins (you’re 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.

I fact you do not need to use a graph db, just store required values in one upper level. It is just like storing an Order and OrderItems. you do not have to calculate total each time an order is about to be displayed. Instead you just calculate sum, vat and other stuff and store them with your Order.

order.Subtotal = order.Items.Sum(item => item.Price);
order.Tax = order.Subtotal * 0.25m; // just a value
order.Total = order.Subtotal + order.Tax;

// fast forward time
var subTotal = order.Items.Sum(item => item.Price);
var tax = subTotal * 0.25m;
var total = subTotal + tax;

if (toal == order.Total) {
   Console.Log("Why the hell I've just re-calculated total?");
}

I suggest to look at the Command Query Responsibility Segregation pattern.

Basically instead of create a single model to read and write you can create 2 different models. One optimized for updating and the other optimized for queries (read, reporting, …). The 2 models are synchronized (usually with eventual consistency) using domain events (see DDD).

I started to study this pattern some months ago and it really changed my way of modelling software. It is not easy because It is a big shift, especially when used with other techniques like DDD and Event Sourcing. But for worth it.

There are many resource available on the net, search for CQRS and DDD (and eventually Event Sourcing).

This pattern can be used on both SQL and noSql.

In your case you can fire an event each time the nutrients are changed to update the read model that is optmized for reading. The read model can be for example a denormalized view of the menu’s nutrients (why not using a nosql db for efficient read). You can have multiple read models based on the queries that you need to perform.

There are some implications using this approach but it is very scalable and extensible.

It greatly depends on how you do to get the menus and the nutrients initially. Why do you think it will not be efficient?

From what I understand, you go to the DB, get the menu, then go again, get each recipe, then go again and get each ingredient and so on. This is really inefficient, as there are a lot of queries and round-trips to the server, which is the main source of delays. This is known as the SELECT N+1 problem.

What you should do is to fetch all the data in a single query, by using JOINs for all tables from the menu up to the nutrients, so the DB server can use all the relationships and indexes to get the data all at once. The client C# app only processes and display the final result. Doing so is much more efficient than going one by one.

In general, using proper querying techniques and the right indexes for critical queries, relational databases can perform very well on big tables under load.

It looks like you’ve spent some time thinking about how best to model the data so that it can be easily updated and queried. However, now you are at the point where you need to provide access to the data. Those two things are separate concerns.

You mention reloading the page is causing a new query to the database. You also mention that the database will occasionally be updated and when it is you want those updates to be displayed on the page in a timely manner. Your best method to reduce the overhead of queries is not to do them. If you’re running the same queries over and over and getting the same results, why not cache them for a while? You should be able to implement some caching upstream without modifying the rest of the project. I would recommend reading about rest. Regardless if you implement the project in an rdbms or nosql problems with performance of this type are best handled by reducing the number of times you have to go to the database. Say you have 100 requests for the same recipe in 60 seconds. If you cache for 60 seconds then you only hit the database once so that’s a 100x improvement in performance. To see that same level of improvement by switching to nosql is going to require a lot more work.

Nosql type systems can be a great solution when you have huge amounts of data or extreme read or write speed requirements. However that extra performance comes at a cost of throwing out things like referential integrity.

It seems like for the experiment or knowledge purpose you want to try Graph-DB but your example is clearly an example of hierarchical data where we can Drill-Down/Up through a node. I am not expert on Graph/Neo DB yet I can see there are not much complexity in the way user/you can request data from this schema. I see the choice of database/schema design is very depends on how and what type of data will be queried against it. As you using SQLSERVER “HierarchyI”D is the best option from my point of view to put this nodes as part of Tree.

My suggestion is to think like a machine and not like a human. It may seems repetitive, but that what machines are good at. One thing that you have to ask yourself is “do I have to retrieve every object, anyway, to display on my page?” If yes, continue what you are doing, compared to data retrieval, cpu cycles are negligible when doing simple math.

Filed under: softwareengineering - @ 07:01

Thẻ: c++, database-design, graph-databases, nosql, sql

Thiết kế website giá rẻ

Danh mục

Nested Entities and calculation on leaf entity property – SQL or NoSQL approach