All we need is an easy explanation of the problem, so here it is.
I am trying to design a database solution for the following:
-
A table containing all nutritional information regarding a given ingredient; ingredient_ID is the PK here but ingredient name has a 4 character unique constraint upon it too (so
OATSfor a record containing nutritional information on oats) -
A ‘join’ table containing a meal name, say
BRKFST, ingredient_ID, and the given quantity for that ingredient -
Possibly a third table containing just meal name
The first table has already been created so if that could be borne in mind I would appreciate it.
The inclusion of the third table is where I am struggling. I have read through topics on related matters, here, here, and here, but none of these quite seem to answer my query or deal with different designs such that I cannot see how they would adapt to my own. I have also read through the post here and it led me to the current design I have sketched out.
As it stands, this is what I have:
with meal_name and ingredient_id forming a composite PK in the second table. This is due to each record referring to a meal (and potentially an ingredient) multiple times.
So BRKFST might have two records; one for OATS, and one for MILK; similarly, MILK may appear elsewhere, in another meal.
I just wonder then what the use of the third table is in my design and was considering just having the two but was not sure if I might be missing something here. I have no need to include a description and do not want any recipe instructions.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
If a meal consists of nothing but a name, and that name never changes, and there are no two meals that can have the same name, you don’t need the meal table, because the name is de facto a primary key.
If any of these conditions are not guaranteed, or you might at some later time add other attributes to a meal (like a boolean column vegetarian), you need a separate table for the meals.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0