SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    table design question - items with multiple colors

    Hi again

    I have a question regarding table design and items that have multiple colors.

    Let's say my current table looks likes this:

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    brand VARCHAR(55),
    brand_url VARCHAR(2083),
    item VARCHAR(255);

    My page just lists all of those items, and then when click on one of them I am taken to that specific items information page.

    On each item information page, if the item has a different color, I want to be able to click that color and have the images on the page change to the item in that selected color.

    I thought of a way to do this, but I'm not sure it is the best way or if it would be smart. I would love some advice.

    What I came up with was adding a 'default_color' column, and then only list items on the main page sorted by 'default_color'. But in my database, add 'item_color' and add just insert each item multiple times; one for each color. So on the main page, each product type will only show up once (I don't want every color of every item showing up, just the 'core' product'), but on each items specific information page I can list the colors and have them as links to the correct item color which will reload the item page with the item in the chosen color.

    I also thought of adding item_colors and just listing each color for each item like "red, black, blue" but I don't think I can work with that the way that I need to...

    Anyone have any thoughts on this? It is much appreciated.

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Create an itemcolors table, with itemid and color as columns.

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Create an itemcolors table, with itemid and color as columns.
    Awesome idea, thank you!

    Would that be the best way also including the following information:

    1 - The items that are listed on the main page, they will be listed as pictures of the items. As I said I don't want to display 5 versions of each item, but I want the item that is displayed on that main page to be the 'main' color, not the alternate colors.
    2 - 80% of my items will not have alternate colors. The items that do are likely to have unique color names such as 'Sunfire Red', not just 'Red'.
    3 - Users have a personal profile page that has a list of their favorite items, and I want them to be able to pick a specific color version of an item to be shown on their user page.

    If so, I'm assuming this works like...on the main page I simply list all the items, nothing changes there...and I only use the itemcolors table on each specific items page to check if there are any alternate colors, list those colors as links to change the images on said items page? Is that right?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    That sounds ok

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    That sounds ok
    Good stuff, thanks very much for the help!

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have a follow up question to this.

    When inserting colors into the itemcolors table. What if I want to list an item as having multiple colors, but I want that item to come up if someone searches for any one of the listed colors?

    for example:

    itemid, color
    '1', 'black + red'

    If someone searches for either black OR red, or both, item 1 should show up.

    But how should I list it:

    'black, red'
    'black + red'
    'black/red'

    etc.

    Is there a 'proper' way I should be formatting that?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oknow View Post
    Is there a 'proper' way I should be formatting that?
    yes... one row per colour
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes... one row per colour
    Aha! Of course.

    Thank you very much for the tip.


Bookmarks

Posting Permissions

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