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!

you need a separate table for ingredients

I thought about that... but is it really needed? How would you set up the relations then?

yes it is really needed

where would you store ingredient information then? redundantly for each supplier?

Well then, how would I store the price of the ingredients? The price depends on the supplier....

...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 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

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 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.

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%