I'm trying to create a site related to recipes where users can add custom meals, and within meals they can add foods and then list the ingredients of the foods. For example:
Meal = Dinner
Food = Hamburger
Ingredient = Tomato
Users can select foods from a pre-filled database of food items. They can also add ingredients from a central ingredients database. So the same food and ingredient ids can be used by any user of the site.
The challenge I have is ensuring the food_has_ingredients table is linked to the user somehow. In the diagram below the meal is associated to the user. Since the foods and ingredients ids can be re-used by multiple users I can't rely on those ids to determine which user adds ingredients to a food.
Take this example:
User 1 adds the food "hamburger" (id 10) to the meal_has_food table, then associates the ingredients "ketchup" (id 20) to the food_has_ingredients table.
User 2 adds the food "hamburger" (id 10) to the meal_has_food table, then associates the ingredients "pickles" (id 21) to the food_has_ingredients table.
If someone does a search get all ingredients from the meal hamburger (10) and limit it to one row, it would always give the first person's entry. So I've associated the meal id as a foreign key that way I can say get the ingredient for hamburger that belongs to meal id X.
I'd like to know if that makes sense or if there's a better way.
Click the image below to see the schema: