Stock management of assemblies and its sub parts (relations)

Posted by The Disintegrator on Stack Overflow See other posts from Stack Overflow or by The Disintegrator
Published on 2010-06-03T04:29:05Z Indexed on 2010/06/03 9:54 UTC
Read the original article Hit count: 404

Filed under:
|
|
|

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 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. 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

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql