views:

71

answers:

2
+1  Q: 

MySQL set storing

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?

+5  A: 

I believe the wise thing to to is add another table:

CREATE TABLE `products_variations`(
  `product_id` int NOT NULL,
  `variation_id` int NOT NULL,
  PRIMARY KEY  (`product_id`, `variation_id`)
);

From there, a simple query joining the three tables will give you the results you need.

Seb
Ah, clear as water, thanks!
Rob
+1  A: 
SELECT  GROUP_CONCAT(variation_id, ', ', name, ', ', kind SEPARATOR '-')
FROM    products
INNER JOIN
        variations
ON      FIND_IN_SET(variation_id, variations)
GROUP BY
        product_id

As it's a many-to-many relationship, it's better to have a third table:

CREATE TABLE product_variations
(
   product_id INT NOT NULL,
   variation_id INT NOT NULL,
   PRIMARY KEY (product_id, variation_id)
);
Quassnoi
Would this be better than the answer above? I'm not very familiar with MySQL's performance.
Rob
No, @Seb's answer is great. My answer is good only if you already have a schema you need to live with. If you can change the schema and add the third table then do it for the God's sake.
Quassnoi
Thanks Quassnoi, that's really helpful!
Rob