Storing Lists of Values

[punk_runner]

What is the proper way to store a list of values in MySQL?

 

For example, I have a product that is available in 4 colors: "blue, red, green, white" and

it is also available in three sizes: "small, medium, large"...

 

Should I store those in fields called product_colors and product_sizes as strings like this:

$product_colors = "blue, red, green, white";

$product_sizes = "small, medium, large";

 

and then SELECT them and explode them when needed, or is there a better way to do it with normalization?

 

I have 4500 products and probably 200 different colors and 15 sizes between them... if I store all of the colors in one table

and the sizes in another, how do I relate them back to a specific product?

 

A problem I run into is when they are in a comma seperated list and I SELECT it, it creates a complex array() when it comes

to using a templating engine, that must loop over the main array and then colors and sizes child arrays need looped over too.

[cmattoon]

Simple Solution:

-Products -------------- Colors --------------- Sizes

-Foo        --------------- Red ------------------ Small

-Foo      ---------------- Red ------------------ Med

-Foo      ---------------- Red ------------------ Large

-Foo      ---------------- Blue ------------------ Small

-Foo      ---------------- Blue ------------------ Med

.....etc

(SELECT * FROM table WHERE products='foo')

 

Better:

Products Table

-- ID --- Description

    1      Foo

    2      Bar

 

Colors Table

-- ID --- Color

  1        Blue

  1        Red

  2        Blue

  2        Red

 

Sizes Table

-- ID --- Size

    1    Small

    1    Medium

    2    Small

    2    Medium

 

 

Let's say we're on 'products.php?id=1'... shopping for "foo".

We create dropdown boxes by:

<select name="color">
<?php
$sql = mysql_query("SELECT * FROM colors WHERE id='$id' ORDER BY color ASC");
while($row = mysql_fetch_assoc($sql)){
     echo "<option value=\"".$row[color]."\">".$row[color]."</option>";
}
?>
</select>


then...........


<select name="size">
<?php
$sql = mysql_query("SELECT * FROM sizes WHERE id='$id' ORDER BY size ASC");
while($row = mysql_fetch_assoc($sql)){
     echo "<option value=\"".$row[size]."\">".$row[size]."</option>";
}
?>
</select>

 

You may also want to add a numerical value for sizes, so they show up in a logical order, not alphabetical:

 

ID size sizetxt

1 1 Small

1 2 Medium

1 3 Large

2 1 Small

2 2 Medium

2 3 Large

 

This would show Sm/Med/Lg in that order... not "Large/Medium/Small"... or worse "Large/Medium/Small/X-Large/X-Small/XX-Large/XX-Small"

In this case, you would do

echo "<option value=\"".$row[size]."\">".$row[sizetxt]."</option>";

The 2nd method makes it easier to update your inventory... say you now offer Purple "foo".. just add a row to the "colors" table, and the sizes will pre-populate. This doesn't take into account that Red foo doesn't come in 2XL, only yellow and blue, etc...


Disclaimer: There is probably a better/more efficient way to do this... but this is just my 0.02. (I'm no SQL expert, but I do use it quite a bit)

[ignace]

product (product_id);
product_color (product_color_id);
product_size (product_size_id);
product_quantity (product_id, product_color_id, product_size_id, quantity);

[punk_runner]

// stuff

 

 

Yeah, that's all pretty standard but what if one product is only available in two of the colors and another is available in three colors etc... there has to be a way to tie a foreign key for the colors and sizes fields back into the products table. I think that is what ignance was getting at but his explanation lacked any explanation LOL.

[ignace]

I think that is what ignance was getting at but his explanation lacked any explanation LOL.

 

Indeed, I also accounted for quantity. In cmattoon's design at best this is spread between 2 tables which makes it hard to maintain and prone to error. The tables product, product_color, and product_size respectively holds the product, the possible colors, and the possible sizes (duh!).

 

The product_quantity ties them together:

 

INSERT INTO product_quantity (product_id, product_color_id, product_size_id, quantity) VALUES
('T-Shirt #1', 'Red', 'S', 2),
('T-Shirt #1', 'Red', 'M', 10),
('T-Shirt #1', 'Red', 'L', 5)
('T-Shirt #1', 'Blue', 'S', 1),
('T-Shirt #1', 'Blue', 'M', 5);

 

For convenience I used text instead of numbers although in the case of size you could use the label as PK. Due to foreign key constraints you won't have a Blue and Bleu T-Shirt which was possible in cmattoon's design. The design also makes it easy to find how many of T-Shirt #1 you have in stock regardless of size or color:

 

SELECT name, sum(quantity) FROM product_quantity JOIN product USING (product_id) WHERE product_id = 1

 

Or per color of a particular T-Shirt:

 

SELECT color, sum(quantity) FROM product_quantity JOIN product_color USING (product_color_id) WHERE product_id = 1 AND product_color_id = 1

[cmattoon]

//stuff..

+1

 

I've been working with lots of linked tables recently, where each tables' data is pertinent to each other (personnel records)... that does make it a lot easier though, and what i was starting at with part 1.

 

Out of curiousity, why four separate tables? (I may be missing something obvious here... lol)

[ignace]

Out of curiousity, why four separate tables? (I may be missing something obvious here... lol)

 

Normalization