I have to track the stock of individual parts and kits (assembly) and can't find a satisfactory way of doing this. Sample bogus and hyper simplified database:

Table prod:
prodID      1
prodName    Flux capacitor
prodCost    900
prodPrice   1350 (900*1.5)
prodStock   3
-
prodID      2
prodName    Mr Fusion
prodCost    300
prodPrice   600 (300*2)
prodStock   2
-
prodID      3
prodName    Time travel kit
prodCost    1650 (1350+300)
prodPrice   2145 (1650*1.3)
prodStock   2

Table rels
relID       1
relSrc      1 (Flux capacitor)
relType     4 (is a subpart of)
relDst      3 (Time travel kit)
-
relID       2
relSrc      2 (Mr Fusion)
relType     4 (is a subpart of)
relDst      3 (Time travel kit)

prodPrice: it's calculated based on the cost but not in a linear way. In this example for costs of 500 or less, the markup is a 200%. For costs of 500-1000 the markup is 150%. For costs of 1000+ the markup is 130% That's why the time travel kit is much cheaper than the individual parts

prodStock: here is my problem. I can sell kits or the individual parts, So the stock of the kits is virtual.

The problem when I buy: Some providers sell me the Time Travel kit as a whole (with one barcode) and some sells me the individual parts (with a different barcode) So when I load the stock I don't know how to impute it.

The problem when I sell: If I only sell kits, calculate the stock would be easy: "I have 3 Flux capacitors and 2 Mr Fusions, so I have 2 Time travel kits and a Flux Capacitor" But I can sell Kits or individual parts. I have to track the stock of the individual parts and the possible kits at the same time (and I have to compensate for the sell price)

Probably this is really simple, but I can't see a simple solution. Resuming: I have to find a way of tracking the stock and the database/program is the one who has to do it (I cant ask the clerk to correct the stock)

I'm using php+MySql. But this is more a logical problem than a programing one