Categories
Database Development

Optimizing product variant db schema

I am trying to model a db schema for product variant. Here is a snippet of my db schema related to product variant

----------------------      ------------------------
|skus                |      |sku_variations        |
----------------------      ------------------------
|PK| sku_id          |      |PK| sku_variation_id  |
|FK| product_id      |------|FK| product_variant   |
|                    |      |FK| sku_id            |
----------------------      ------------------------

Currently, if a product, lets say a shirt, have 2 variant with 2 variant options each (for example, color: black, white and size: small, large) there would have to be 8 rows in sku_variations that would represent that

--------------------    ---------------------------------------------------
|skus              |    |sku_variations                                   |
--------------------    ---------------------------------------------------
|sku_id |product_id|----|sku_variation_id      | product_variant | sku_id |
|1      |1         |    |1                     | 1 (size small)  | 1      |
|2      |1         |    |2                     | 3 (color black) | 1      |
|3      |1         |    |3                     | 1 (size small)  | 2      |
|4      |1         |    |4                     | 4 (color white) | 2      |
--------------------    |5                     | 2 (size big)    | 3      | 
                        |6                     | 3 (color black) | 3      |
                        |7                     | 2 (size big)    | 4      |
                        |8                     | 4 (color white) | 4      |
                        ---------------------------------------------------

Not to mention that if some other product (lets say another shirt) also have the same variant as the example shown above, it would have identical data but with the sku_id being different. I am also confused as how to index table sku_variations.

What i am trying to do, i don’t know if this is recommended or possible, is map every combination to a table, have an id represent said combination, and just use that id as a foreign key on the skus table.

For example, i would have an id that represent the combination black and small. I would have another id that represent the combination black and large.

The db that i am trying to design should be able to handle any amount of variation, not limited to only 2. Is it possible? Or is my example already ok? I am just worried about data redundancy.

This is what i have so far for the product portion of my database schema.

  • table variants holds values such as size, color, material, etc
  • table attributes holds values such as black, white, wood, big, small etc.

Leave a Reply

Your email address will not be published. Required fields are marked *