Product quantity bound to colors and sizes table structures

Question

I've been thinking about how I'll be able to create products with sizes and colors which will match the max quantity of a product (or their total will be the max.quantity ).

Let's say I got a product which has a total 12 quantity.

In those 12 there are colors such as RED,BLUE,GREEN. AND there will be 3 sizes such as SMALL, MEDIUM, LARGE

Like:

3x RED SMALL
1x RED LARGE

2x BLUE SMALL
2x BLUE MEDIUM
1x BLUE LARGE

1x GREEN SMALL
1x GREEN MEDIUM
1x GREEN LARGE
Total:12X

Now, sometimes some products wont have any colors, only sizes. Sometimes only colors, no sizes. Sometimes no color neither sizes.

What is the best way of structuring tables to acheive this?

I think like:

Products: PID,name,(maxqty?)

Sizes: SID, name

Colors: CID,name

Used_sizes_colors: PID,SID,CID,qty

How would you do this?

Answer 1

The model you described looks good to me.

You can add a trigger that will work on UPDATE and INSERT and will check if SUM(qty) from Used_sizes_colors table for updated or inserted PID is greater than Products.maxqty, and if it is: rollback transaction and display error.