Normalization Question

[punk_runner]

When building a products database, specifically clothing, how do you guys handle database normalization when it comes to sizes and colors?

 

Correct design dictates that you have a products table, a colors table, a sizes table and product_colors and product_sizes tables to tie them all together, right?

Should the product_colors and product_sizes tables be tied together into one table with color and size columns, or kept separate?

 

I have it mapped out on our dry erase like this, is this correct?

 

 

colors_table

id  color

---------------------

1  blue

2  red

3  green

4  orange

5  black

6  white

7  purple

8  navy

9  gray

 

 

sizes_table

id  size

---------------------

1  small

2  medium

3  large

4  x-large

5  fatty

 

 

products_colors_table

id  sku        color 

-------------------------------

1    2155      1   

2    2155      2

3    2155      3   

4    2155      4   

5    2155      5   

6    2155      6

7    2155      7

8    2156      1

9    2156      3

10  2156      5

11  2157      2

12  2157      7   

 

 

products_sizes_table

id  sku        size 

-------------------------------

1    2155      1   

2    2155      2

3    2155      3   

4    2155      4   

5    2155      5   

6    2156      1   

7    2156      2   

8    2156      3   

9    2156      4   

10  2156      5   

11  2157      1

12  2157      2

13  2157      3

14  2157      4

 

 

products_table

id  sku        desc      price

-------------------------------

1  2155      shirt      13.99

2  2156      pants      14.99

3  2157      bra          free

[Jessica]

I personally would cut out the products_colors_table and products_sizes_table and in my products_table have a field for size_id and color_id.

 

Or one products_options table

option_id, option_value, option_type

1, 'red', 'color'

2, 'small', 'size'

3, 'blue', 'color'

4, 'square', 'shape'

[mikosiko]

@punk_runner

 

Your design is correct imho, and should be the one that give you the most flexibility

[punk_runner]

I personally would cut out the products_colors_table and products_sizes_table and in my products_table have a field for size_id and color_id.

 

Or one products_options table

option_id, option_value, option_type

1, 'red', 'color'

2, 'small', 'size'

3, 'blue', 'color'

4, 'square', 'shape'

 

The idea is to repeat as little information as possible in the database, right? What I am worried about is that if I have a product that comes in three colors and three sizes, I have nine varieties, and if I have one row for each of them in the products_table, each of those rows has the same description, title, price etc... that seems repetitive. This is a large site, they did $550,000 in sales last year and expect $2M this year, so my redesign has to really be robust.

[fenway]

If you break this out into another table, you're just looking at yet another join.

[ignace]

I thought I had already answered this question.