Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26

    Unanswered: is this a good layout for a food db?

    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*)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?

  3. #3
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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*

  5. #5
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26
    ok. this seems too difficult for me to handle for now.
    maybe i'll start with just a db with the groceries i use and as i understand more about db's i can build on it.

    by * i mean foreign key (its the way its written in the book i am currently learning from.)

    this is the information i need about my groceries.
    name, price, calories
    so i'll make a table like this:
    FOOD(id, name, price, calories, category)
    do i need to normalize this?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demiurgen
    so i'll make a table like this:
    FOOD(id, name, price, calories, category)
    do i need to normalize this?
    that is normalized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by demiurgen
    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).

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by demiurgen
    so i'll make a table like this:
    FOOD(id, name, price, calories, category)
    do i need to normalize this?
    Do you need to retain historic pricing?
    For example an apple purchased in 2007 may have been 4p less than one bought in 2008!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •