Conditional logic:
- There is a preparation "Greens" weighing 100 grams. It consists of products "Dill" and "Parsley" - 50 grams each.
- There is a preparation "Sauce" weighing 160 grams. It consists of products "Potato", "Dill", and "Parsley" - 100, 30, and 30 grams respectively.
- We create the preparation "Fry". It consists of the product "Beef" 100 grams and the preparation "Greens". In the composition of the "Fry", the preparation "Greens" changed its weight (instead of 100 grams, it now weighs 40 grams), meaning the weight of the products changed ("Dill" and "Parsley" weigh 20 grams each). This change was recorded in the table.
- We create the preparation "Broth". This preparation includes "Fry", but its weight has been changed by the user (instead of 120 grams, it now weighs 300 grams). Accordingly, in the composition of the "Broth", the ingredients of the "Fry" changed weight - "Beef" weighs 215 grams and the preparation "Greens" weighs 85 grams ("Dill" and "Parsley" - 42.5 grams each).
The question is how to build such a database schema so that it can maintain the nesting "Broth"->"Fry"->"Greens"-> and so on (any nesting) taking into account the change in the weight of nested ingredients? Changes in the weight of the nested preparation and its products should not affect the parent preparations. That is, when querying, for example, the preparation "Broth", it should immediately return the weight of "Fry", the weight of its ingredients, and also (importantly!) the weight of the preparation "Greens" and its products ("Dill" and "Parsley"). For this, a separate set of ingredients should be created in the composition of the preparation. Please advise how I can set up the creation of nested entities with a set of ingredients considering weight changes. Where should I look?
If, for example, I managed to handle one level of nesting "Fry"->"Greens" (with products), then deeper nesting like "Broth"->"Fry"->"Greens" (products) or deeper nesting doesn't work. The difficulty is that copies of the composition of the nested preparation need to be made. Maybe I should use a column with JSON data and put all the nesting in there?
