Spiga

Database design example for a configurable product eshop

by Gabi Solomon

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.

  • http://www.modularhomesnetwork.com/ Manufactured Homes

    This was a good post. I am new here but now I’ll visit often.

  • prasad sawant

    Can You Explain this in More Details

    • http://www.gabrielsolomon.ro/ gabi solomon

      @google-a2eda87d70d107591bae0638253421c2:disqus what part is confusing ?

  • Neobeetle

    I am also creating a eshop based on yii. it will be have product options and a different price. i as you inspected different database designs (magento, opencart, zendcart etc) and a think that yours is very simple. my design is very similar to yours except i didn’t use the configuration and created a separate table for that. 
    http://www.весы.com.ua

  • Pierre B

    Hi, thanks for sharing your design.

    Some things aren’t clear to me though: What is the “selection” attribute for?
    How does the id_configuration works? how do yo link products together?

    Also, for example, if you have different prices for features, how would you manage it?

    • http://www.gabrielsolomon.ro/ gabi solomon

      Hello,

      The selection field was somethin specific for a project.
      The link between products in the same configuration is that id_configuration. When creating a configurable product i woul generate an unique ID and save it in id_configuration.

      Do you want to set price for a feature or for a certain configuration?
      The ideea i wrote was to have a product for each combination so you have full control of the price.

      • Pierre B

        Hey, thanks for your quick reply!

        So, just to be sure if I got it right, I’d like to discuss an example.
        Let’s say I have a t-shirt with a logo on it.
        The t-shirt is available in 2 colors, for men and women, and in 4 different sizes.
        Prices are different for the man and the woman version.

        So in your design I would create 2 “color” features, 1 “men and 1 “women” feature and 4 “size” features…?

        Then I would have to create 2(colors)*2(gender)*4(sizes) = 16products, with the same id_configuration? And then the eight for men have a price and the eight for women have another price?

        Is that how it’s suppose to work?

        • http://www.gabrielsolomon.ro/ gabi solomon

          Hello,

          you should create a color feature with 2 values (ex: white and blue) one gender feature with 2 values (men, women) and one size feature (s,m,l,xl)
          and then 16 products for each combination.and this will enable you to also keep stock information for each combination since in real life and accounting you will need to have a separate SKU, price and stock value for each combination.

          • Pierre B

            Ok I see. Thank you very much for your time! :)