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

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?

Quote:

---

Originally Posted by r937

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.