SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design for Meal Plans

    Looking for some help on how I might design a database for meal plans.

    I'm following Kevin Yank's book on PHP & MySQL and I think I have a case for many to many relationships. I have the following structure so far, but I am not sure if it is the most efficient design?

    The problem I am solving for is to create a Plan consisiting of n number of meals (meal 1, 2, 3, 4, 5) with various selections of food per meal.

    Example: Plan A contains 6 meals(breakfast, snack, lunch, snack, dinner, snack) with 3 food types per meal(eggs, oats, banana).

    The coachid is used to allow different coaches to have different names for food, meals, plans. I'm trying to make this multi tenant as well.

    Any help would be much appreciated and please be gentle, this is my first database driven design

    Tables:

    food
    ---
    id
    foodname
    coachid

    meal
    -----
    id
    mealname
    coachid

    plan
    -----
    id
    planname
    coachid
    userid

    Lookup Tables:
    foodmeal
    ---------
    foodid
    mealid

    mealplan
    ----------
    mealid
    planid


    Thanks,
    Jim

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm a little lost on the first table

    it seems to imply that each foodname belongs to only one coach

    thus, if nineteen coaches have a food called "eggs" there would be nineteen rows in this table

    seems a bit unusual, no?

    also, what's a tenant?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm a little lost on the first table

    it seems to imply that each foodname belongs to only one coach

    thus, if nineteen coaches have a food called "eggs" there would be nineteen rows in this table

    seems a bit unusual, no?

    also, what's a tenant?
    I'm using tenant in terms of an apartment. One big building, multiple tenants or renters.

    I'm building an app in which their would potentially be multiple coaches each with their own set of clients that get meal plans.

    You're correct in your view of the first table. My client basically wants each coach (tenant) to be able to have their own custom descriptions for things that the other coaches can't view. Nothing shared, unfortunately.

    So they way I was going about it was to connect the coachid to each food, meal, plan etc so I could display only that coaches entities. I agree with you that having a single shared food, exercise, etc would be best but the client insists that it should not be shared across coaches.


Bookmarks

Posting Permissions

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