I would like to be able to enter a fooditem (eg. ‘Apple’) or a meal (which consists of fooditems (eg. ‘ApplePie’ with ‘Apple’, ‘Pastry’ ) into a food diary table. I’m thinking of the following structure:
- A food diary table consisting of the following columns: fooddiaryID (PrimaryKey), date, foodID, and foodtype (ENUM(‘fooditem’, ‘meal’). The foodID together with the foodtype could then be used as a key to select the corresponding fooditem or meal.
- A fooditem table consisting of fooditemID, foodtitle, calories.
- A meal table consisting of mealID, mealtitle, totalcalories (by joining to a junction table where I can enter multiple fooditemIDs to mealIDs – I can do this)
However, I don’t know how to link the foodID/foodtype from the food diary table to the fooditemID or mealID.
I tried using a foreign key on foodID but this can only reference one table.
Would anyone be able to help?
New contributor
Dr B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.