Inventory with multiple attributes

Question

In my product table, I have a field representing a set of attributes.

A set of attributes is a set of attributes (size, color, etc.).

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

How can I store inventory information for products with multiple (and non-fixed) attributes?

Answer 1

I would suggest instead of violating NF1 to use the Attribute attribute of an object . This is not without problems. The wikipedia article explains this and includes alternatives, including the use of sparse columns or XML rows.

Answer 2

You can 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 

Answer 3

You can get a many-to-many relationship between Products and Attributes in a relational database by entering a “ connection table ” (called ProductAttribute here) to hold associative objects :

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