Postgresql – Proper form in relational meal database

database-designpostgresql

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:

enter image description here

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.

Best Answer

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.

Related Question