hard database design

[keoflex]

I am building a database that handles food and nutrition intake. The database i am designing is fairly difficult so i was hopping i could get some help from the community.

--------------------The database does the following----------------------
1. track food intake aka total 'sugar', 'protein', vitamin B12 for all users
2. track food nutrition based off of individual products aka eggs, toast, etc.. or track via a combination of foods that make a recipe aka

[nutrition info]------>[nutrition line]<------[foods]----->[food line]<----[recipe]

the measurements are done on a daily basis so users can see a running total for the current day or a history of previous days of nutrition elements they are consuming.

This would be relatively easy without the recipe field but now Im in a situation where recipe's have many food groups which have many nutrition values as opposed to just having the food groups.

an example of my diagram is at www.keoflex.com/dbview.png

Anyway with the fact that I have nutrition values applied to food applied to recipe's i dont know how to distinguish between individual foods and full recipe's which causes a problem because the user might have 1 egg for breakfast but have cake made with two eggs and other products at lunch.

Please help im still a bit of a noob at designing these things

[pclfw]

Just a few thoughts which may be of help.

People {Users} eat food. This food might be a recipe or a primary food (i.e. an egg). They also eat quantities of both food and recipes. So you will need a many to many relationship between the users and the recipes and the users and the foods tables. Both of these link tables will need to hold date / time food (or recipe depending on the table) and quantity.

The links through these relationships will enable you to sum the nutritional values from the nutrition table via the food table.

[keoflex]

so i would need to create two more table aka food_intake_line and Rec_intake_line, put date and time field on both and a user_id relationship with the users table. if a user selects a recipe it will log that in the rec_intake_line and if they select an individual food it would be recorded in the food_intake_line

then when i want to get the totals i just write a query to pull nutritional values for both foods and recipes during the desired range. Does this sound rite?

Just a few thoughts which may be of help.

People {Users} eat food. This food might be a recipe or a primary food (i.e. an egg). They also eat quantities of both food and recipes. So you will need a many to many relationship between the users and the recipes and the users and the foods tables. Both of these link tables will need to hold date / time food (or recipe depending on the table) and quantity.

The links through these relationships will enable you to sum the nutritional values from the nutrition table via the food table.