i am trying to build a little db for myself that contains all the groceries i use. that shouldn't be so difficult but i also want to (with php) put different groceries together to form a meal and store different meals in another table. and later i want to try and put different meals together and store as daily consumption.
is this layout good enough? seems a bit tiny...
FOOD(ID, name, price, calories)
MEAL(ID, total_price, total_calories, food_id*)
no.. it may be a start though
why would you have total_calories and total_price in a meal?
surely theses items are the sum of food eaten, if so you should track what foods comprises the meal. Knowing the food eaten, gives you both the price and the calorific value in the meal
does the meal comprise of only one item of food?
how do you propose to handle the situation where say someone eats 2 of one item of food.
how would do you propose to handle stuff like say food composition (eg sugar, fats, carbohudrate etc....
generally speaking I'd expect
several meals per day/week
each meal to be comprised specific recipies
each recipie may contain one or more ingredients
each ingredient contains details of its cost, calorific value, nutritional values etc......
optionally a recipie may be used in more than one other recipie (for example many flour based sauces use the same basic stock (a white sauce).
is it a bit tiny?.. I don't know, what is required for your assignment?
i guess i don't have to store total price and calories in the db (just calculate and display it in the browser.)
a meal could just be one item yes, an apple for instance.
another meal could be bread, butter, ham, cheese and milk.
but the quantity is something i forgot about.
what if i add a table like this:
MEAL_DETAILS(ID, quantity, food_id*)
i won't bother with food composition on this project.
it is mainly the calories consumed per meal and the price of it that i want.
so how do you work out what is comprised in a meal, bearing in mind a meal may be an apple, 2 apples, a stew, a bowl of cereal
how do you define quantity
is it by weight, volume, length or area
how do you handle equivalence? for example you may use tinned tomatoes in a recipie. but you can buy several different sizes (200g, 400g) and types (plum,chopped, chopped in juice, garlic, chilli flavoured) of tinned tomatoes each one of which has a different cost and calorific value
can a meal comprise more than one element (eg say a 3 course meal)
It may well be my ignorance but I dont recognise what you mean by food_id*
by * i mean foreign key (its the way its written in the book i am currently learning from.)
I did wonder if that was how you meant to indicate that a meal may be comprised of multiple food_id's.. Id sort of taken it as read that the it was a fk to your Food table. Its so long since Ive used notation like that, these days my data models are cooked (if you'll pardon the pun) using a GUI modelling tool.
so just guessing that you are still struggling in the foothills of db design (no harm in that we all have to start somewhere...., and to be honest I suspect its really only the like of Rudy (R937) & PatP and a few others on here who are possibly struggling at the summit)...
have you looked through the standard references on things like normalisation....
I found the bit from Paul Litwin on Rudy's site a very good clear and concise way of getting to grips with what goes where during table design. It may get over cited (espcecially by me, but it still has its merits).