0

I am trying to model a database containing the following tables:

Meals (MealId)
MealRecipes (MealId, RecipeId)
MealFoods (MealId, FoodId)

Recipes (RecipeId)
RecipeFoods (RecipeId, FoodId)
Foods (FoodId)

A Recipe is composed of N Foods (Apple, Salmon, ...)

But when I build a Meal it can contain Recipes (Salmond with vegetables) but also a Food (Apple) ...

My biggest problem is with building the Meal with both Recipes and Foods.

UPDATE
One important thing is the order of the Recipes / Food in a Meal. So another approach would be:

Meals (MealId)
MealSteps (MealId, StepNumber, RecipeId, FoodId)

Where RecipeId or FoodId, either one, could be null. But not both.

Miguel Moura
  • 36,732
  • 85
  • 259
  • 481
  • Seems like you already did that... or I don't understand the question. – Zohar Peled Mar 30 '17 at 10:34
  • Can't quite see what the question is. Your proposed tables seem reasonable. – Clay Mar 30 '17 at 10:35
  • I added an update with another approach I am considering ... – Miguel Moura Mar 30 '17 at 10:37
  • My main problem is how to build a Meal with Recipes and Foods being Recipes and Foods of two different types (from different tables) but I might want to add extra information on how both integrate in the Meal like the position ... – Miguel Moura Mar 30 '17 at 10:38
  • Either I am over simplifying it and in that case I apologize but I think this should work `Meals (MealId) Foods (FoodId) Recipes (RecipeId, FoodId) MealComponents(MealId, RecipeId, FoodId)` – Gouri Shankar Aechoor Mar 30 '17 at 10:42
  • @GouriShankarAechoor Where RecipeId or FoodId in MealComponent could be null, correct? Look at my update. I think you are saying the same as my MealSteps – Miguel Moura Mar 30 '17 at 10:44
  • 1
    A meal will consist of food and a Recipe would also be made up of food as such if a Meal only has a RecipeID, that meal would still contain food. However if more than one variety of a food item needs to be part of a meal then that could be bundled into a Recipe. – Gouri Shankar Aechoor Mar 30 '17 at 10:47

2 Answers2

1

Your solution looks fine. A meal is composed of different recipies (e.g. one for the main dish, one for the salad) plus single food (e.g. an apple for dessert).

Another way to model this would be to drop the table MealFoods and make one-food recipies instead. (E.g. an apple by itself is a "recipe" for a simple dessert. Salt on the other hand is not.)

So with this model you even prevent some food to be added to a meal. You could do the same with a mere flag in Foods in your model, too, that allows apple to be added to a meal and salt not. But that would rely on you to consider the flag whenever inserting into MealFoods, whereas in the second model you can only add recipes to meals, which makes this safer.

UPDATE: Here is the proposed model. You see, I've only removed one table. (And I put in the position you have added in yor update). The trick is that you build some "recipes" that have only a single entry in RecipeFoods, e.g. an apple.

  • Meals (MealId)
  • MealRecipes (MealId, RecipeId, position)
  • Recipes (RecipeId)
  • RecipeFoods (RecipeId, FoodId)
  • Foods (FoodId)
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Can you just post the tables names and Key columns as I did just to make it more clear.? I am not sure I understand how do you prevent something from being added without a Flag and a few more details you talk about – Miguel Moura Mar 30 '17 at 10:42
  • Okay, I've added this to my answer. – Thorsten Kettner Mar 30 '17 at 10:46
  • I see ... So when a a food, let's say an Apple, can be inserted into a Meal you just create a Recipe with only one food (Apple) and nothing else, correct? – Miguel Moura Mar 30 '17 at 10:47
  • Yes, that's it. – Thorsten Kettner Mar 30 '17 at 10:49
  • That is one approach but the problem is that Recipe has a lot of columns and implications in the system and I am not sure I want to create Recipes with only one food in it with no preparation, cooking time, category and so on ... But it is something I am considering ... – Miguel Moura Mar 30 '17 at 10:52
1

I think you have a couple of related concerns here - it might help you to specify the business domain in semi-formal language.

I think it's something like:

The system has many meals.

A meal is made up of one..n courses, in a sequence.

A course is either an ingredient or a recipe.

A recipe is made up of 1..n ingredients (and instructions, presumably).

If that's true, your second data model seems okay - but you have to consider how to model inheritance in your database - both a recipe and an ingredient can make up a course. There's no single best solution to that problem, but you'll find many questions on Stack Overflow to help.

Finally, you might want to consider the possibility that a course would be made up of several ingredients or recipes served together, e.g. soup and bread.

Community
  • 1
  • 1
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • That is exactly my problem! You nailed it ... And before posting this question I was reading about model inheritance and it is not easy to take a decision ... :-) – Miguel Moura Mar 30 '17 at 10:50
  • I think you need to do further work on the domain model to make the decision - is it really just "recipe" and "ingredient"? How about "drink"? And are there different types of "ingredient", e.g. fresh/packaged? You can only make the trade-off once you have a better idea of the object model. – Neville Kuyt Mar 30 '17 at 11:01
  • 1
    IMO there's no reason that a course should have to be *either* an ingredient or a recipe: you can just decide that a course is a recipe, and then like you said, a recipe is 1..n ingredients. 1 is a valid choice here. – DrewJordan Mar 30 '17 at 11:10
  • Yes, I just opted by one Meal have N recipes and 1 Recipe have 1 to N ingredients. It makes more sense. – Miguel Moura Mar 30 '17 at 11:14