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.
BRKFST might have two records; one for
OATS, and one for
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.