Practical mysql schema advice for eCommerce store - Products & Attributes

Question

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?

Answer 1

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.