Proper form in relational meal database

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 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:

Proper form in relational meal database

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

Leave a Reply