Help setting up database....to create a new table or not to create a new table....

anthony522

I am setting up a databse of products and each product has some product specific information (like ID#, description, etc.) each product page should also display the nutrional information for the product. This where I find myself in a quandry and I'm sure its because of my lack of database creating experience more than anything else.

The nutrional information varies for product, all have warnings and usage instructions but some also should display a list of ingredients and the amounts (mgs) for each ingredient. I'm not sure whether I should just use one table and create say 20 columns that would represent 10 ingredients (one for amount and one for name) or if I should just have 10 columns in the table that would have both the amount of mgs and the ingredient listed in each. I would then set the empty ones to default to 0 so when I looped through the results it would just skip displaying one's with a zero.

Does anybody have any suggestions about what they think would be the best way to set this all up? If I'm unclear feel free to ask me to clarify because I don't think I'm explaining this too well.

Thanks for any help.

ahundiak

Traditional design calls for three tables:
product - product_id, description
ingredient - ingredient_id, name
product_ingredient - product_id, ingredient_id, amount

With this design you need not worry about the number of ingredients per product and you can easily query for products by ingredient.

anthony522

Originally posted by ahundiak
Traditional design calls for three tables:
product - product_id, description
ingredient - ingredient_id, name
product_ingredient - product_id, ingredient_id, amount

With this design you need not worry about the number of ingredients per product and you can easily query for products by ingredient.

I wonder if this would bring up any problems when trying to setup the admin page. Or would I just go page by page for each table to update one "product" so I could get the ID. I guess I could go page by page passing each variable along and then once I got to the end I could give them a confirmation and have then send the update.