4

I am currently planning my first eCommerce application (mySQL & Laravel Framework).

I have various products, which all have different attributes. Describing products very simply, Some will have a manufacturer, some will not, some will have a diameter, others will have a width, height, depth and others will have a volume.

Option 1: Create a master products table, and separate tables for specific product types (polymorphic relations). That way, I will not have any unnecessary null fields in the products table.

Option 2: Create a products table, with all possible fields despite the fact that there will be a lot of null rows

Option 3: Normalise so that each attribute type has it's own table.

Option 4: Create an attributes table, as well as an attribute_values table with the value being varchar regardless of the actual data-type. The products table would have a many:many relationship with the attributes table.

Option 5: Common attributes to all or most products put in the products table, and specific attributes to a particular category of product attached to the categories table.

My thoughts are that I would like to be able to allow easy product filtering by these attributes and sorting. I would also want the frontend to be fast, less concern over the performance of the inserting and updating of product records.

Im a bit overwhelmed with the vast implementation options, and cannot find a suitable answer in terms of the best method of approach. Could somebody point me in the right direction?

In an ideal world, I would like to offer the following kind of functionality - http://www.glassesdirect.co.uk/products/ to my eCommerce store. As can be seen, in the sidebar, you can select an attribute the glasses to filter them. e.g. male / female or plastic / metal / titanium etc...

Alternatively, should I just dump the mySql relational database idea and learn mongodb?

1
  • Would adding something like Apache Solr or ElasticSearch for the search part be an option? In my experience with very complex data of the kind you describe this would allow you to design your database freely without having to worry too much about highly complex SQL queries. Jan 15, 2014 at 13:49

1 Answer 1

5

I think the key thing here is flexibility. Since there are different types of products with each having different set of attributes, it would be fair to assume that product as well as attribute types will change. So it isn't very convenient to include attributes in the product tables.

Here is what you could do:

  1. have a table of product types
  2. have a table of products with a many-to-one relationships to the product types table
  3. have a table of attribute types
  4. have a table of (many-to-many) relationships between product types and attribute types
  5. have a table containing attribute type, attribute value, product primary key

This design allows a huge flexibility. The tables are normalized, there is no redundancy, there is no need to alter any table schemes if the definition of a product changes. If the database is properly indexed and other search optimization techniques are implied as thorsten müller suggested, this solution will work.

3
  • 1
    Thanks - So is your solution the equivalent of my option 4? With reference to point 5 - My attribute value would need to be datatype varchar and I would let the application decide how to handle the datatype?
    – Gravy
    Jan 15, 2014 at 14:36
  • It is mostly similar to point 4, with some additions (maybe those were assumed in point 4). You'll indeed need to have varchar type for attribute values. Regarding point 5, I think it's risky in terms of flexibility and is overkill. You'll be able to store all attributes without having any of them in the products table.
    – superM
    Jan 15, 2014 at 14:42
  • 1
    Could you store the specific value for a product/attribute in the many to many table between PRODUCT and ATTRIBUTE instead of creating another table? Would love to know your thoughts. Many Thanks!
    – johnsnails
    May 29, 2014 at 1:57

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