How to model that several variants of a merchandise item may affect its price?

Question

I am an absolute beginner to relational data modelling. I hope my question will be acceptable here nevertheless.

Let’s say there’s a merchandise. An item of merchandise may have variants.
(While it also has attributes common to all variants).

I don’t want to limit in the data model what the variants can be. I know the merchandise can vary in size and in color, but I don’t want the model to be limited to these two possible variations. There might be other variants (such as in material or process).

It is not clothing, and the variants may be priced differently. While I expect mostly sizes to affect the price, I don’t want to impose any such limit.

I also wish to anticipate that several variants could combine to affect the final price. Say that a different process could affect the price and different sizes too but colors not. But I want to avoid any fixed limitations in the model.
This is what I don’t know how to model properly.

(BTW, variants are identified as such by adding a variant code to the base of their SKU.)

Answer 1

If the variants are identified by a variant code at the base of their sku, is the variant code always in the same place? If so, you can always group together with the base sku.

However, you might want to look at product grouping. As an example, if your catalog of products is exercise products. And you have fitbits. The Fitbit flex can be sold with different colored bands. Each one would have a sku of 1234 with a letter variant. So bl for black, bu for blue, pk for pink. so 1234bl is black fitbit flex.

Now you can set up categories. So the next level would be the type of fit bit. You also carry the fitibit Charge. That sku is 2345. So one item can have a sku of 1234bl, a subcategory of Fitbit, a category of pedometer, under the store area of technology. And you can group the data at those various levels. A vp of Electornics might only care about the Category and the technology level. Yet a Category manager cares about the sub category and category. They may also care at the sku level.

Does this answer what you are looking for?

Answer 2

This sort of question is a fairly regular star on this forum.

You essentially appear to be asking about implementing an EAV (Entity Attribute Value) system. Bill Karwin (a big hitter on this forum) has a hilarious image which cleverly shows why this model should normally be avoided. Karwin has written a book about SQL anti-patterns and devotes his first chapter to this very pattern.

Another big hitter in the SQL world (Joe Celko) calls the model the OLTP (One True Lookup Table - perhaps a touch of sarcasm?) and, more tellingly, MUCK (Massively Unified Code Key) - see here.

There is not universal agreement that the EAV model is fundamentally flawed - Magento (or here for the community edition) uses it - primarily in the fashion industry and Aaron Bertrand (another big hitter on this group) has this to say about the matter.

My own recommendation is for you to have a separate table for every item type - keep objects which are similar (i.e. share the same characteristics or fields) in the same tables and make good use of SQL to mix and match combinations rather than trying to do everything in your schema design in one step. Also, RDBMSs these days can have many hundreds (even thousands) of tables without problem.

As I pointed out in beginning, this has been discussed many times - take a look at the threads here - (not just my answers :-) ) - but until you're experienced, I wouldn't go down the EAV route if I were you. Pay particular attention to Joe Celko's SQL article - the SQL gets very hairy very quickly with the EAV model.

Finally to see an EAV model taken to extreme lengths (and the madness that this can lead to) check out this article. I can also highly recommend the entire OakTable book - Tales from the Oak Table, one of the best IT books I ever read. Some reaction here on StackExchange.

Two more posts which are well worth reading. This (about 5 common database design mistakes - one of the clearest articles I've read) and this (driving my points above home).