Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Question Unanswered: Many-to-many relationship?

    Hi there,

    I'm building a database intended for menu planning for a backpacking trip. Currently, I have three tables:
    • A list of the days for which menus must be planned
    • A list of the meals planned for these days
    • A list of all the food items, their weights and calorific content

    There are many-to-one relationships between the food items and meals tables, and between the meals and days tables. So I can put in meal types and calorie targets without redundancy.
    However, at the moment I can't have a food item appearing in more than one meal, or quantities of more than one of a food item per meal. This means if I want to include the same thing multiple times (eg cereal bars) I have to type everything in again.
    Is the solution to this a many-to-many relationship (a food item can be in multiple menus and a menu can contain multiple food items)? If so, what would the junction table be?

    Secondly, is there a way round the fact Access can only use the current table for calculated fields? (I'd like to be able to sum the calories and weights of individual food items to give figures for each meal)

    Thanks in advance for your help
    Attached Thumbnails Attached Thumbnails Relationships.PNG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    modelling a many to many relationship in a relational DB is not immediately obvious. The best way to do so is to use an intersection table which uses the primary keys of the 'parent' tables as the intersection table's PK. that intersection table stores everything that is relevant to that specific intersection. sometimes that could just be the primary keys of the parent tables, but it could also include other information that is a result of that intersection
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I would re-do the relationship structure. tblDays, as the least amount of detail, can be the driving table (meaning, all the way on the left).

    In tblMeals, don't make MealNumber a PK, because PK must be unique, and there will be a meal number 1 (breakfast) every day of your hike. Instead, have DayNumber and MealNumber be a compound PK, as there will only be one Day1/Meal1 combo on the hike. tblMeals, as less detailed than tblFood, should be in the middle.

    I hope you include solid and liquid snacks in this table.

    tblFood, then, should be on the right.

    I think your many-to-many problem disappears if you do this.

    You can now make a report to list every single day and meal, including each detail food with quantity, weight, and caloric content. You can then add a totals column, and come up with a total planned weight, etc. using the above scheme.

    Sam

    PS. Be aware that you're limiting yourself to only one type of breakfast (for example), so if you're going on a 16-day hike, I hope you like Cheerios (or your favorite breakfast) a lot, because that's all you're programming for, unless you have the compound PK in tblMeals.

  4. #4
    Join Date
    Oct 2012
    Posts
    2
    Hi Sam,

    Thank you for the reply - have I interpreted you correctly (attached)? It seems like I'm still doing something wrong because this implies that each meal contains only one item of food.

    Cal
    Attached Thumbnails Attached Thumbnails Relationships2.PNG  

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You have the relationship between tblMeals and tblFood backwards. Make it one-to-many from tblMeals to tblFood. That way you can have many foods in a single meal.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •