GS Design Just another developer blog

I was building an e-shop and one of the requiremens was to have configurable products.
The configurable parts will need to have different prices and stocks from the main product.  Also the shop must be able to move products from one category to another.

So i started thinking of a database structure that will enable me to accomplish this. As i started to research i also posted a question on stackoverflow to see what others had in mind.

The design

After a bit of thought i came up with this design.

Design Details

Products have an id_configuration
A configurable products actually is a collection of products witch have the same id_configuration (witch is auto-generated).

Features linked to products
Rather then categories, which seems more naturally to me. This enables me to easily move products between categories.

Product features values is stored in the link table
I decided to store the feature value for a product in the link table. This helps with more simple queries and also it means that you don’t have to insert all the options in the feature options table (think of features that have different values for all products)

Name and Label for features
The name is used internally in the admin section and Label is used in the site front-end. This is useful for features with same name. Ex: Size for clothes, size for hardisks etc.

Filterable flag on features
A Yes/No flag that shows witch filters are filtrable.

Examples of use

Selecting products
The most important thing is how do we select products, and how do we only show one product for the configurable ones. For this we use GROUP BY.

SELECT * FROM `products`
GROUP BY CASE `products`.`configurable` WHEN 'yes' THEN `products`.`id_configuration` ELSE `products`.`id` END


Filtering products
The bellow example shows the query for a single filter/feature (with id 38) and 2 values selected:

SELECT * FROM `products`
WHERE `id` IN (SELECT id_product FROM `product-features` WHERE `id_feature` = 38 AND `value` IN ('Test value', 'New Value') GROUP BY id_product HAVING COUNT(*) >= 1)
GROUP BY CASE `products`.`configurable` WHEN 'yes' THEN `products`.`id_configuration` ELSE `products`.`id` END


Get active filters
After you have a list of filter products you will want to print out a list of filters that are applicable to this list, since not all are available to the current selected products.

SELECT `id_feature` FROM `product-features`
WHERE `id_product` IN (SELECT `id` FROM `products` WHERE ___YOUR_CONDITIONS____) GROUP BY `id_feature`

Notice that i left out the GROUP BY from the product subselect.

Associate count of products for a filter
Its very useful to show the count of products for a certain value of a filter. The bellow query is how you can select that count for one filter.

SELECT *, COUNT(*) AS `count` FROM (
SELECT *
FROM `product-features`
WHERE
`id_product` IN (SELECT `id` FROM `products` WHERE ___YOUR_CONDITIONS____) AND
`id_feature` = 36
) AS `tbl` GROUP BY `value`

Notice that i left out the GROUP BY from the product subselect.

Conclusions

I think this database model works best for what i wanted and it has all the flexibility i needed:
- it allows for any 2 products to group as configurable
- you can add promotions and separate images for each variation
- have separate stocks on each variation (witch make more sense to me)
- get sales report cumulate or separate on each variation.

Hope you like this approach and i await you comments on it.