4

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.

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

1

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.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

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

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255) 
  • Thank you. Hmm, at the moment we have over 400 variations in a spreadsheet, each one about 15-20 attributes, so that would give about 6000-8000 entries in the attributes table. Wouldn't it be difficult to manage? 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. It would be great to be able to specify which attributes are applicable for a given product. PS. I'll add this to my question. – ellockie May 29 '14 at 12:37
  • 2
    There should be a third table containing attribute names so that a comparison can be made across products. This is called Entity Attribute Value and is generally frowned upon, but I consider it to be the perfect for product catalogs. See: stackoverflow.com/questions/11779252/… – Joel Brown May 29 '14 at 12:38
  • 1
    @ellockie: Managing 6000-8000 records manually is difficult. That's why databases were invented. Properly designed and indexed, an attribute table with 8000 records should not be too much of a problem. – FrustratedWithFormsDesigner May 29 '14 at 14:17
  • 1
    EAV it is then I'd say – James Anderson May 29 '14 at 15:06
  • 1
    The attributes table will give you your dropdown while the attributevalues table can be used to store different values for each attribute and product. – James Anderson Jun 2 '14 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.