The trouble with stock

[ucozade]

Looking to see if anyone has any smart ideas?

I have a stock database and it is consisting of three parts. The first part is the parent. This is the complete item that people can buy. E.g a car.

The next level is a sub-parent. The pieces that go into making the car like the engine and wheels.

The lowest level is the parts that go into making these. Like the rubber, nuts and bolts.

I need it so that orders can be taken for the top level and for spares, the second level. When this occurs I need it so that the data base will take out the stock from the bottom level to build the pices for the higher levels.

E.g 1 car ordered is equal to 1 engine, 4 wheels and 1 car body.

1 engine is equal to 30 bolts and 50 kg of metal from stck and so on.

Then the same again but, with people only buying the engine.

How is it best that I relate through the tables to change the quantity of stock from the top to the bottom?

 

[gemma-the-husky]

tricky - you need to think about this a lot. I assume when you make an assembly you book all the parts out, and then book an assembly in as stock.
By the same way when you make a car you book out the assemblies and finish with a completed car in stock.

Therefore all these items are really just stock and you should just have a single stock file rather than a parent/assembly/item structure. HOWEVER you do ALSO need to relate parts to each other as assemblies, as you note, for prodcution schedules, stock pick lists and so on. So perhaps you need another table to store these relationships ie that a car requires 1 each of certain subassemblies plus a quantity of a certain tyre, and that an asembly is made up of certain base parts.

I don't know how far you will go in this, because this is moving swiftly towards an all-encompassing product/stock, or costing system. You will have loads of issues about consumables and maybe other parts that are issued by weight not qty, you may issue floor stock for general use products that are not issued in specific quantities for your asemblies. you may need to deal with waste/scrap and so on. How do you deal with stock outs if you are trying to pick items for an assembly.

As I say, I would give this a lot of thought. Make sure you understand all the business processes you need to manage, and all your data workflows. But what sounds quite straightforward may end up being very very complex