I have a couple of tables in a web application I'm coding in PHP, and I would like to know if this would be good pratice.
CREATE TABLE `products`(
`product_id` int NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`variations` varchar(255) default NULL,
PRIMARY KEY (`product_id`)
)
CREATE TABLE `variations`(
`variation_id` int NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`kind` varchar(255) default NULL,
PRIMARY KEY (`variation_id`)
)
For example, a product will be:
1,'Cup','1,2,3'
While a variation would be:
1,'Green','Color'
2,'Glass','Texture'
3,'Blue','Color'
such that many products could have the same colors/textures. The issue I find is that I cannot fit this into a single query that would return the data as:
1,'Cup','1,Green,Color-2,Glass,Texture-3,Blue,Color'
And afterwards parse this accordingly to display an image of each variation.
Is a stored function that returns that format the best idea? Or should I normalize even further, and if so, how?