Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Arrays or tables?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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