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?

 

[Luddite Lad]

I have a data base that is a little similar in that the product can be sold as a set, or as component parts. What I have done is, I have a table that holds the description of the complete item, the components and prices are then stored in a second table which is linked by the ID for the complete item.

So I guess that you will need three tables, one for the complete item, another for the components and a third for the individual parts.

 

[Luddite Lad]

I wouldn't have bothered answering this, if I had seen there were two threads on the same topic and the other had already been answered.

I will add however that all my DB does is draw up a purchase orders for my supplier and produce an invoice for my client, as I’m not interested in the stock control side of thing as it’s not my problem.