Optimum design / structure for product data database


What would be the optimum design/structure for product data database, where each product have many variations and many attributes? Attribute sets vary between products, and some attributes have values common for all variations of a given product. A typical example of product variations & attributes table is in the image below:

Example of attributes table

(The 'ATTRIBUTE X' is here to give you and idea of a different attribute type than for 'Product 1').

Would it be practical to try to 'group' some of the attributes (to save time and minimise errors when creating new product entries)? If so, how complicated would it need to be?

If it's relevant – I'm thinking of using mySQL engine, but I'd be happy to see any applicable example from other engines.

More information: at the moment we have over 400 variations in a spreadsheet, each one about 15-20 attributes, so that would give about 6000-8000 'data points'. It would be great to be able to list all possible values for a given attribute (to create a dropdown menu when adding new entries), and also to have a choice of possible attributes for a given product. It would be great to be able to specify which attributes are applicable for product variations of each product.

Best Answer

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)