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