Practical mysql schema advice for eCommerce store - Products & Attributes
- by Gravy
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?
