Storing dishes in the database and linking them to meals and products

[nyok]

Now: Each meal item is recorded separately.

Example: Buckwheat with chicken breast

meal table

id	product_id	datetime	portion
56	10	2022-12-06 19:21:40	110
57	16	2022-12-06 19:22:13	120

products table

id	title	proteins	fats	carbohydrates	calories
10	Chicken breast boiled	21.00	2.50	0.00	110.00
16	Boiled buckwheat (1 buckwheat + 2 water)	4.00	1.14	21.40	117.00

---

But what to do with more complex dishes (soups, salads)

Example: Crab salad consisting of 3 ingredients.

• eggs, grams
• crab sticks, grams
• corn, grams

List Products

id	title	proteins	fats	carbohydrates	calories
14	Egg	12.70	11.50	0.70	157.10
27	Crab sticks	9.50	0.10	13.30	92.00
28	Sweet corn	2.87	0.60	9.90	57.80

Salad ingredients

products	grams	proteins	fats	carbohydrates	calories
4 Egg	280	35.56	32.2	1.96	439.88
Crab sticks	250	23.75	0.25	33.25	230.00
Sweet corn	140	4.02	0.84	13.86	80.92
Sum	670	63.33	33.29	49.07	750.80

Result

products	grams	proteins	fats	carbohydrates	calories
Crab salad	100	9.45	4.97	7.32	112.06

[nyok]

Variants solution:

1 Calculate the nutritional value separately and write it down in one line in the table "products"


2 Keep records of dishes (dishes - products composition):

In meal table (replace connection product_id to dish_id)

id	dish_id	datetime	portion
99	1	2022-12-06 19:22:13	120

Create dish table

id	dish name	dish_products_id
1	Crab salad	1

Create dish_products table

id	dish_id	product_id	portion
1	1	14	280
2	1	27	250
3	1	28	140

Also, it's possible add columns (proteins, fats, carbohydrates, calories) to the dish table - which were recalculated when added to the dish_products table

OR

In meal table add column (dish_id) and then one dish_products table could be created (which would have references to the ingredients),
And the name and nutritional value remain (and are recalculated) as before