Stock management of assemblies and its sub parts (relationships)

Question

I have to track the stock of individual parts and kits (assemblies) 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 1200 (900+300)
prodPrice 1560 (1200*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. So, 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

Update: Sadly Eagle's solution wont work.

• the relationships can and are recursive (one kit uses another kit)
• There are kit that does use more than one of the same part (2 flux capacitors + 1 Mr Fusion)
• I really need to store a value for the stock of the kit. The same database is used for the web page where users want to buy the parts. And I should show the avaliable stock (otherwise they wont even try to buy). And can't afford to calculate the stock on every user search on the web page

But I liked the idea of a boolean marking the stock as virtual

Answer 1

Okay, well first of all since the prodStock for the Time travel kit is virtual, you cannot store it in the database, it will essentially be a calculated field. It would probably help if you had a boolean on the table which says if the prodStock is calculated or not. I'll pretend as though you had this field in the table and I'll call it isKit for now (where TRUE implies it's a kit and the prodStock should be calculated).

Now to calculate the amount of each item that is in stock:

select p.prodID, p.prodName, p.prodCost, p.prodPrice, p.prodStock from prod p where not isKit
union all
select p.prodID, p.prodName, p.prodCost, p.prodPrice, min(c.prodStock) as prodStock
from 
  prod p
  inner join rels r on (p.prodID = r.relDst and r.relType = 4)
  inner join prod c on (r.relSrc = c.prodID and not c.isKit)
where p.isKit
group by p.prodID, p.prodName, p.prodCost, p.prodPrice

I used the alias c for the second prod to stand for 'component'. I explicitly wrote not c.isKit since this won't work recursively. union all is used rather than union for effeciency reasons, since they will both return the same results.

Caveats:

• This won't work recursively (e.g. if a kit requires components from another kit).
• This only works on kits that require only one of a particular item (e.g. if a time travel kit were to require 2 flux capacitors and 1 Mr. Fusion, this wouldn't work).
• I didn't test this so there may be minor syntax errors.
• This only calculates the prodStock field; to do the other fields you would need similar logic.

If your query is much more complicated than what I assumed, I apologize, but I hope that this can help you find a solution that will work.

As for how to handle the data when you buy a kit, this assumes you would store the prodStock in only the component parts. So for example if you purchase a time machine from a supplier, instead of increasing the prodStock on the time machine product, you would increase it on the flux capacitor and the Mr. fusion.