Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.