So, after coding for several years in Java I have grown very fond of arrays. Coming to mySQL it seems that arrays are something quite novel (as I understand it, in fact, arrays are only in mySQL 5.0 onwards and mySQL just creates a table for you and then treats it as an array). I have tried reading into the subject but the explanations don't seem to cover the best practice, only that you can use arrays.

This query has come about because I have a database that can be used to tell you how much fat, protein and carbohydrate is in a given food. It includes the following tables; Ingredients and Portions. Ingredients holds information of each ingredient of a food (a Portion) per 100g e.g. flour or egg. Portion holds the amount of each ingredient as a percentage, so that when a weight is specified you have the weight of each ingredient within the portion. So if the portion was 100g of batter, and it was 50% flour, 50% milk, the database would tell you the amount of fat/protein/carbs in 50g of flour + 50g milk.

The Ingredients table is easy enough:

Ingredients
ID (Primary Key) - Int
Name - String
Carbohydrates - Int
Protein - Int
Fat - Int

The Portion table is a bit more tricky:

Portion
ID (Primary Key) - Int
Name - String
[List of Ingredient and their associated percentages]

For the information in italics, do I:
a.) Create a new table for each portion and make the italicised field a reference to the following table:
[Portion.Name]
Ingredient.ID (Foreign Key) - Int
Percentage - Int

OR

b.) Create the arrays ingredientIDArray[] and percentageArray[] where the value of index of one corresponds to the value at the index of the other. Then I could replace the above italicised field with fields for the arrays.

My instinct is to go with the arrays, however I do not know if arrays are to be trusted in mySQL. Any input from an experienced user would be fantastic.

first of all, i don't know that mysql supports arrays at all, so if you've found something on their site which suggests they do, please share a link

secondly, you don't need arrays to support what you want to do

besides the ingredients and portions tables (and i would recommend another name for the portions table) you need a third table, suggested name portion_ingredients, which has one row for each ingredient in a portion, with foreign keys portion_id and ingredient_id, primary key consisting of the two foreign keys together in a composite key, and a data attribute indicating how much of this ingredient is in this portion

vwalah!