3 Replies Latest reply on Oct 19, 2014 9:01 AM by philmodjunk

    Meal diary database



      Meal diary database


      Hello everyone. 

      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:

      - Patients

      - Diaries

      - Days

      - Meals

      - Join_Meal_Food

      - Foods


      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?


      Other Questions...

      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.


        • 1. Re: Meal diary database

          Patient has many Diaries.

          Diaries have many Days

          Days have many Meals

          Meals have many Foods.    and you have a join table for this many to many relationship

          • 2. Re: Meal diary database

            Probably I'm not been really clear. Sorry. I'll try to make questions more precise. I'm trying to use Anchor-Buoy method, with tables I've wrote in the first post. I'm going to explain what I've done.

            In "Patients" layout I'd like to see how many diaries each person has got.  So I've made a relation PT_PATIENTS::Id = pt_DIARY::patientIDFK, built layout based on PATIENTS table, with a portal based on pt_DIARY.

            Now, I'd like to see the days belonging to the selected diary. I've made two relationship:

             pt_DIARY::Id = pt_diary_DAYS::daysIDFK


             PT_PATIENTS::SelectedDiary = pt_diary_DAYS_selected::daysIDFK  that helps me to select a diary in the portal and  see its days in a second portal (that is based on pt_diary_DAYS_selected).

            Now, I'd like to see meals belonging to the selected day, but If I create another portal for "MEALS" in this layout, It could be a mess, isn't it?. What do you think about? Anyway, I create another Layout based on DAYS but (here's the 1st probelm) how can I go from the selected day in the 2nd portal of PATIENS Layout to tha same day but in the DAYs Layout?

            And (2nd problem) : In the DAYS Layout, I'd like go navigate (thank, for example, to two arrows, on the side of the day's name, one for previous and one for next day) though the days belonging to the same diary (that I've selected as above) of one patient, how can I get that? 

            And (3rd problem) : If I create new record in Days Layout, I should say that this day belonging to a certain diary of a certain patient, What should I do?

            Until now I've created these relationships:

            - DAYS::diaryIDFK = days_DIARY::Id 

            - days_DIARY::patientIDFK = days_diary_PATIENTS::Id

            - DAYS::Id = days_MEALS::daysIDFK

            any suggestions? Thanks to whom will help me... :)

            • 3. Re: Meal diary database

              I don't see why an additional portal for Meals set up  so by selecting a day in the portal of days, you get the meals for that day in this new portal would be a problem. It's just a logical extension of the same technique that you already have working for you.