I must track the stock of person parts and kits (devices) and should not look for a acceptable method of carrying this out. Sample bogus and hyper simplified database:

Table push:

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 package

prodCost 1200 (900+300)

prodPrice 1560 (1200*1.3)

prodStock 2

Table rels

relID  1

relSrc  1 (Flux capacitor)

relType  4 (is really a subpart of)

relDst  3 (Time travel package)


relID  2

relSrc  2 (Mr Fusion)

relType  4 (is really a subpart of)

relDst  3 (Time travel package)

prodPrice: it's calculated in line with the cost although not inside a linear way. Within this example for costs of 500 or less, the markup is really a 200%. For costs of 500-1000 the markup is 150%. For costs of 1000+ the markup is 130% This is exactly why time travel package is a lot less expensive than the person parts

prodStock: here's my problem. I'm able to sell kits or even the individual parts, Therefore the stock from the kits is virtual.

The issue after i buy: Some companies sell me time Travel package in general (with one barcode) plus some sells me the person parts (having a different barcode) Then when I load the stock I'm not sure how you can impute it.

The issue after i sell: Basically only sell kits, calculate the stock could be easy: "I've 3 Flux capacitors and a pair of Mr Fusions, and so i have 2 Time travel kits along with a Flux Capacitor" However I sell Kits or individual parts. So, I must track the stock of the baby parts and also the possible kits simultaneously (and I must make amends for the sell cost)

Most likely this is actually simple, however i can't visit a simple solution. Returning to: I've to find away out of monitoring the stock and also the database/program is the one that needs to get it done (I cant request the clerk to fix the stock)

I am using php+MySql. But this really is more may well problem than the usual programing one

Update: Sadly Eagle's solution wont work.

  • the associations can and therefore are recursive (one package uses another package)
  • You will find package that does use several of the identical part (2 flux capacitors + 1 Mr Fusion)
  • I really should store something for that stock from the package. Exactly the same database can be used for the site where customers are interested the various components. And That I should show the avaliable stock (otherwise they wont even attempt to buy). And should not manage to calculate the stock on every user search on the internet page

However I loved the thought of a boolean marking the stock as virtual