2 replies to this topic

[Stasonix]

I need to connect product with colors, for example:
my product with id = 5 can have a three colors, yellow, blue, green,
another one product with id = 10 can have five colors, blue.. green... orange (5 colors)
I have table structured, something like this

`product_table`
`id` `name` `foto` `info`

how I can to join this with colors?
Need I one more table?
So what structre it must to be?

[Vaielab]

You have multiple way to do so

You could have a varchar columns in your product_table, with the name of the colors separated by comma.
This technics make it easy to implements, but hard to read, it's harder to search via colors, you will have to search with regex or like and it's a lots slower.

You could create a other table with colors (id color and name color) and add 5 (or more) columns to the product_table
So product_table will have `id` `name` `foto` `info` `color1` `color2` `color3` `color4` `color5`
But I would not recommand this because it will make your query string a lot bigger (... where color1 = 'blue' or color2 = 'blue' or color3 = ...)
You will be limited to 5 colors, and when other products only have 3 colors you will have to use some db space for nothing.

And the last way you could do (the one I recommand)
Will be to create a color table with id and color name, and a second intersect table to link the color with the products that will only have the id of the product and the id of the colors
So now you will be able to have unlimited colors for each product, and it can be pretty fast to search

select * from product_table pt inner join productColor pc on pc.idProduct = pt.id inner join color c on c.id = pc.idColor

And if you wish to find only blue product you add a where at the end like so

where c.name = 'blue'

[Stasonix]

Thnx for your answer Vaielab, now I will do maybe a first example, 'caz third one it's difficult for me, but think in future I'll return to it. I think it's will be interesting not only for me.