1

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.

1 Answer 1

0

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.

5
  • I should have clarified, BRKFST will not change, but the composition may. So originally BRKFST is perhaps OATS and MILK, later MSLI, MILK, BRRY. I envisioned the latter 'overwriting' the former. Would this change anything?
    – twelfth
    Mar 26, 2021 at 16:37
  • Changing meal_ingredients is not a problem either way. Mar 26, 2021 at 16:39
  • Thank you, that answers my query.
    – twelfth
    Mar 26, 2021 at 16:40
  • Surely changing meal ingredients is a problem if one wishes to record what was actually served over time? You'd have to have some sort of temporal table recording what BKST was over time? If you're keeping track of either nutrition or kJoules or cost (or all three), you have to know what was served for BKST at time x - or have I misunderstood?
    – Vérace
    Mar 26, 2021 at 18:37
  • @Vérace There is a lot of things that could also be part of a database for meals. But we don't know the use case, and a focused question that concentrates on one aspect is a good thing. Mar 27, 2021 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.