Meal diary database
I need help with a database I'm working on. I've tried lots of solutions, but the only result I've got is a kind of mess, so I hope somebody can help me to re-organize ideas.
The explanation is a bit long, but I really hope someone will dedicate time to helps me overcome this catch that keeps me stationary for too many days!
Please sorry for my English and forgive me for all the errors you'll find.
So, the database helps me to organize information about patients and their meals stored in a weekly diary. Each patient has more than one diary. Each diary has more than one day (could be one or 7 or 14, it depends..). Each days could has more than one meal (eg: breakfast, lunch, dinner, after dinner, extra-meal...). of course Each meal has multiple aliments.
I've created these tables:
I've linked them through these relations:
Patient::id = Diaries::PatientIdFK
Diaries::id = Days::DiariesIdFK
Meals::id = Join_Meal_Food::MealsIdFK
Foods::Id = Join_Meal_Food::FoodsIdFK
Now, when I select a diary I'd like to see days stored in that diary. When I select a Day I'd like to see only the meals of this day. and so on.
I can't understand which table I should use to create a layout or if I should create different Layouts (in this case, how can I navigate from Layout1 to Layout2 showing only related records?).
At this moment I've created a Layout based on Patients with a portal based on Diaries, where I can select a diary (through a button with script "Set field [Patients::SelectedDiary; Days::Id]") but then? I should create a layout based on Days, but can I see days of selected diary? what should I create?
In table Foods, each food has many details, like Kcal, Proteins, Fats etc..
In JoinMealFood each record is a food, selected by a dropdownlist based on table Foods. So I can make a list of foods eaten by the patient in a specific meal, and by writing how many grams of this food are consumed a calculation shows me the amount of Kcal, Proteins, Fats etc.. for these grams. Then, in another field, another calculation shows me the sum of Kcal, Proteins, Fats,, etc...eaten in that specific meal. Ok, problems:
1° this sum works only in a layout based on Meal's table with a JonMealFood's portal.
2° at the end of the compilation of the entire diary, I need to see a sort of day by report, with all those sums (eg Kcal of a meal and Kcal of the day).
Thank You to every answer You'll give.