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.

Improve this question
6

1 Answer 1

Reset to default
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)
Improve this answer
9
  • 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, 2014 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, 2014 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, 2014 at 14:17
  • 1
    EAV it is then I'd say
    – James Anderson
    May 29, 2014 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, 2014 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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