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,
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.