table design question

    Date: 10/17/07 (MySQL Communtiy)    Keywords: no keywords

    A company is currently storing one colorID for each product. The colorID is included in the main product_list table. Reporting on different colors is pretty straightforward:

    SELECT * FROM product_list WHERE colorID=1 OR colorID=2;

    A list of matches appears, one row per productID. Simple and easy.

    Now the company wants to add multiple colorIDs per productID.

    The best way to do this (I think?) is to create a product_color lookup table containing productID and colorID.. This way unlimited numbers of colors could be stored per productID.   Alternately, I could add two fields to the product_list table (color_id_2, color_id_3) but I'd really rather  not go there. 

    In either case, I have a dilemma.   As soon as multiple colorIDs are assigned to a product my simple report is broken.  Instead of listing each product separately (along with it's corresponding colorID), identical productIDs are listed for each associated colorID.
    SELECT
        *
    FROM
        product_list
        LEFT JOIN product_color ON product_color.productID = product_list.productID
    WHERE
        product_color.colorID IN (1,2)

    REPORT:
    productID      colorID
    1                     1
    2                     2
    3                     2
    4                     1
    4                     2
    Is there an efficient way to retain my product_color lookup table and list all products singularly, with multiple colorIDs on each row?  
    REPORT:
    productID      colorID
    1                     1
    2                     2
    3                     2
    4                     1, 2


    Source: http://community.livejournal.com/mysql/120639.html

« Importing data || SELECT multiple occurances... »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home