Product inventory with multiple attributes

In my table of products I have a field representing an attributeset.

The attributeset is a collection of attributes (size, color, etc).

Each attribute has several values such as blue, green, red etc.

How would I go about storing inventory information for products with multiple (and not fixed amount of) attributes?

I would suggest instead of breaking NF1 use Entity Value Attribute model. This is not without its problems as well. The wikipedia article explains this and includes alternatives including using sparse columns or XML strings.

You could do:

products
--------
id
name

attributes
----------
id
name
value

product_attributes
------------------
id
product_id
attribute_id

Or

products
--------
id
name

attributes
----------
id
name

product_attributes
------------------
id
product_id
attribute_id
value


SELECT * FROM products,
 (SELECT * FROM product_attributes LEFT JOIN products ON products.id = 
 product_attributes.product_id) AS prod_attr
LEFT JOIN attributes ON prod_attr.attribute_id = attributes.id

You can get a many-to-many relationship between Products and Attributes in a relational database by introducing a "junction table" (here called ProductAttribute) to hold associative entities:

Product  <--one-to-many-->  ProductAttribute  <--many-to-one-->  Attribute
--------                    ----------------                     ---------
PK: id                      PK: id                               PK: id
    name                    FK: product_id                           name
                            FK: attribute_id