Recipe Health risk Database ERD structure

database-design

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.

Best Answer

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.