Single, variable and bundled products Database relations

Hi Lara Folks,

I've been tasked with porting a legacy ecommerce project to laravel it has some interesting product / size, colour, style relations.

And so wanted to know everyones thoughts on how to best tackle the following scenarios:

A product can be just that a single product with no size colour or style variations.

A product can also have variations or options some of which when selected can increase or decrease the price of the parent product i.e A Nike trainer in black, sizes 1 - 5 will use the price associated with the parent product, but the same trainer in sizes 6 - 12 will increase the price by £5 and the same trainer in red will increase the price by another £5

A product can also contain a bundle of products at a unified discounted price, so it could be a trainer as above, socks, and a t-shirt each child product in the bundle containing its own variations / options as in the above example with the possibility of increasing or decreasing the overall price depending on the variation selected for each child product to complicate things a little further users have the option of omitting some products from the bundle i.e they could select no socks which then has to lower the price of the containing bundle.

In the current system the way this is achieved clones product options repeatedly for every bundle created and causes no end of issues when it comes to updating stock.

My initial thoughts are to add a parent_id column to the products table and use this to build a bundle:

id,   parent_id,   name,               price         bundle_item_price
1,     0,                Nike Bundle,  100.00 
2,     1,                Nike trainers   50.00                 45.00
3,     1,                Nike Socks       10.00                 5.00
4,     1,                Nike T-Shirt      60.00                50.00```

Product_options:::
id,       product_id        name             value            price_adjustment
1               2                   colour            black                       0.00
2               2                   colour            red                          5.00
3               2                    size                  5                            0.00
4               2                    size                  9                            5.00```


so in the above example if the socks were removed it would deduct the bundle_item_price from the parent (Nike Bundle) making it £95.00  or if the trainer was added in red in a size 9 it would increase the price by £10 

Has anyone tackled anything like this before and do you think the above is the best way to go about it?

Thanks in advance :)