Recipe Health risk Database ERD structure

Question

I have these tables:

First table(Recipe table),

Recipe

recipeid(PK,AI)

recipename

ingredients

Second table(Ingredients table),

Ingredients

ingredientid(PK,AI)

ingredientname

quantity

Then a 3rd one for display,

Recipe_ingredients

recipeid(FK)

ingredientid(FK)

quantity

How do i construct another table and its relation to the ingredient table to determine its nutritional value.

Should i add another column for nutrition on my ingredient table? Or create another table for nutrition which holds the ingredient id as FK.

Nutrition

Ingredientid(FK)

Say i am diabetic so i should avoid recipes with high amounts of sugar. So from ingredients... im confused where to move from there.

Answer 1

You probably want to have some risk information in your NUTRITION table. Perhaps a text column to describe the risk and a threshold quantity column, which might be optional, to describe what is a safe and an unsafe amount.

The thing you have to decide is whether you want to try to normalize out some other information from your nutrition risk descriptions. For example, do you want to have a MEDICAL_CONDITION table that allows risks to be categorized in terms of what type of person is at risk (e.g. diabetes, nut allergy, lactose intolerance,...)

From a design perspective, we can't answer your question about what is needed. That depends on your business rules and what you want to achieve. We can help with technical design questions based on specific statements of your business rules.

Answer 2

Not 100% sure what you are trying to achieve, but few notes:

In your proposed Nutrition table the class schema would look like this:

It would mean "Nutrition utilizes Ingredient" or "Nutrition HAS Ingredient". Which I believe is not what you need.

Instead you need a relation "Ingredient has Nutrition", so that Nutrition is an attribute or a property of Ingredient. If you need reusable nutrition categories, like "Nutrition level A" etc., you may introduce a separate Nutrition table. Then you will have to add NutritionId foreign key to the Ingredient table.

Otherwise it is just enough to add a NutritionValue attribute to the table Ingredient.