(Database design). Product attributes

Question

When I design my ecommerce projects where products can have multiple attributes (color, size), I usually create common Product entity which holds general information about product and ProductVariant entity which holds attributes and FK to product.

Sample (These are just simple examples, usually attributes (or stock, etc) are stored as FK):

**Product table**
Id     Name       SKU  
1      T-Shirt    sku_1 

**ProductVariant**
Id   ProductID   Name (Optional)      Color      Size     Stock      Barcode
1    1            First               black      XL       50         bar_1
2    1            Second              white      XXL      35         bar_2
3    1            Third               yellow     S        60         bar_3

BUT some developers create this functionality differently: they create single entity Product which holds all information about product (with all attributes), and group by sku when they display it, something like this:

Id    Name      SKU      Color      Size     Stock      Barcode
1     T-Shirt   sku_1    black      XL       50         bar_1
2     T-Shirt   sku_1    white      XXL      35         bar_2
3     T-Shirt   sku_1    yellow     S        60         bar_3

Why the second way is better than variants? May be there are some advantages by storing all values into single table?

PS:// I know this is a "Low-quality" question, and also there are too many questions about Product-Attribute relationship, but I couldn't find the advantages or disadvantage of these designs.

Answer 1

I wouldn't say it's better.
Different designs have different advantages as well as different disadvantages.
You should choose what best suits your needs.
As a rule of thumb, lookup tables save storage space and allows for better normalization, while flat tables allows for simpler (but not always faster!) search queries.

The more data you need to store is more reason for keeping it normalized and indexed properly. For a large amount of data, I would go a few steps further than you and keep a color table and a size table as well.

Answer 2

SKU has to be unique and all shopping feeds use it so it has to be in there. We work in Magento with Magmi and many configurable products (ProductVariants). Every SKU has it's own line with product info and relation to ProductVariants (if applicable).

It's a cleaner and easier table to import/export and importing & exporting is something you'll really want to do with an e-commerce.

Look at it this way, it's fun to have two women but maintaining one is more than enough work.