Tables for Product Components AND Component Combinations

Question

I have a basic understanding of relational databases and would like to assign a number of attributes to an assortment of products. Products examined consistently have four components: model, fabric, color and size; each of which has a corresponding vendor-supplied identifier (ie. 'SSSS.MMMM.CCCC.ZZZZ' = a single product at the size level).

The most streamlined solution would seem to be having a product table that is at the size level, then a table for model, fabric, color and size to assign relevant attributes under each. The problem I am facing is that I would like to assign certain attributes to, say, a given fabric/color combination. Should I then also have tables of each fabric/color combination in this case?

I have laid out roughly what I am referring to above with the below table diagram:

Answer 1

My personal recommendation would be to have a separate table four each of the for attributes listing the different options. This is purely to make it easy to validate data entry.

Then a product for each combination that is required - store transactions against this.

Then a product line table that has several products of different sizes/colors whatever.

Reason for this approach is that a colour/size combination represents a product that should be tracked distinctly from any other size/color of the same thing. A product line might be 'army cargo trousers', and could come in lots of sizes and colours.