Proper form in relational meal database

Question

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 OATS for 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.

Answer 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.