I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes.
From all the research I've done this seems the most likely solution for me and I'm just wondering if there's anything wrong with the following schema and what are the upsides/downsides?
Thank you
Table: products
------------------------------
| ID | ProductName |
|----------------------------|
| 1 | Leather Wallet Case |
| 2 | Jeans |
| 3 | Power Bank |
Table: products_variants
-------------------------------
| ID | ProductId | ParentId | Variant | VariantName | SKU | StockTotal | WholeSalePrice | BuyPrice | OnSale | OnSalePrice |
|---------------------------------------------------------------------------------------------------------------------------|
| 1 | 1 | null | model | iPhone5 | SKU | 10 | 3 | 10 | null | null |
|---------------------------------------------------------------------------------------------------------------------------|
| 2 | 1 | null | model | iPhone4 | null | null | null | null | null | null |
| 3 | 1 | 2 | color | Red | SKU | 10 | 3 | 10 | null | null |
| 4 | 1 | 2 | color | Blue | SKU | 10 | 3 | 10 | null | null |
|---------------------------------------------------------------------------------------------------------------------------|
| 5 | 2 | null | size | M | null | null | null | null | null | null |
| 8 | 2 | 5 | color | Black | SKU | 10 | 3 | 10 | null | null |
| 9 | 2 | null | size | XXL | SKU | 10 | 3 | 10 | null | null |
| 10 | 2 | 9 | material | Cotton | null | null | null | null | null | null |
| 11 | 2 | 10 | color | Red | SKU | 10 | 3 | 10 | null | null |
| 12 | 2 | 10 | color | Blue | SKU | 10 | 3 | 10 | null | null |
| 13 | 2 | 9 | material | Casmir | null | null | null | null | null | null |
| 14 | 2 | 13 | color | Green | SKU | 10 | 3 | 10 | null | null |
| 15 | 2 | 13 | color | Brown | SKU | 10 | 3 | 10 | null | null |
|---------------------------------------------------------------------------------------------------------------------------|
| 13 | 3 | null | null | null | SKU | 10 | 3 | 10 | null | null |
Best Answer
This is just the info from @lesandru reply, I really find it very useful, so credits to him and @sahalMoidu
Applying normalization to your problem the solution is as given. Run and see it on Fiddle
Fiddle