Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    76

    Need Relationship Advice (ha ha... corny!)

    I need to create a database that houses food product information. A primary deliverable will be a report that looks similar to the ‘nutrition information’ found on product packaging:
    http://www.radiantrecovery.com/georgegif.gif

    This is what I have thus far (not complete… I don’t have all fields and whatnot):
    http://www.netphetamine.com/upload/s...brelations.gif

    I’m a bit confused as to how I should store the nutritional information. I’d appreciate any and all suggestions…

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a separate table for ingredients
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Posts
    76
    I thought about that... but is it really needed? How would you set up the relations then?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it is really needed

    where would you store ingredient information then? redundantly for each supplier?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Posts
    76
    Well then, how would I store the price of the ingredients? The price depends on the supplier....

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...another table for ingredient price.

    You should do some research on the principles of database normalization. Otherwise, you are going to find yourself with a lot of bloated code trying to work around shortcuts made in your database design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the ingredient price is dependent on the supplier, then it goes exactly where you have it, in the Supplier_Ingredients table

    all i'm saying is that the columns which define an ingredient (e.g. its name, recommended daily minimum, etc., which are not going to vary by supplier) should go into a separate Ingredients table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you are goiong down the price route, you may also need to cater for product / ingredient equivalance. Ie subtitue product X with Product Y, the fun and games occurs if you also have to substitue product quantities (eg replace a 25Kg bag of X with 10 x 2.5Kg bags of Y.

    if you can avoid the latter like thre plague, it becomes horredndously complex to model
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2003
    Posts
    76
    Quote Originally Posted by r937
    all i'm saying is that the columns which define an ingredient (e.g. its name, recommended daily minimum, etc., which are not going to vary by supplier) should go into a separate Ingredients table
    I realized that I didn't make myself clear and I may be causing some confusion. I'm sorry about this, it's been a long week.

    In my first post, I said:
    "looks similar to the ‘nutrition information’ found on product packaging"
    What I meant to say is:
    "looks similar to the ‘Supplement Facts’ found on product packaging"

    I don't know how to relate the things like vitamins, calories, protien and whatnot to the product. The ingredients are simple, as they are static and don't require an amount. The other things I mentioned, though, require an amount.... and this is where I'm stuck.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    some ingredients have an amount, some have a percentage (of recommended daily amount), and some have both

    Fat 3g 5%
    Cholesterol 15 mg
    Sodium 620mg 26%
    Vitamin A 15%
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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