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:
(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.