Moving Inventory to 2 different location

[Thinh]

I need to track a transaction of an item that could move from inventory(Table A) to a machine(Table C) or to a storage location(Table B) then later move to a machine(Table C). The problem that i face is 1-many relationship in all direction

my three options are
1. Table A 1-Many Table B
2. Table A 1-Many Table C
3. Table B 1-Many Table C

Table A
InventoryID, Quantity

Table B
StorageID, InventoryID, Quantity

Table C
MachineID, InventoryID, Quantity, StorageID?
Either InventoryID is filled or StorageID is filled in.


The problem is i need to track everything meaning all the location the materials has traveled. I have been thinking of combining the Table C and B together to make it work, but haven't been able to find mechanics to make it work. Another thought was applying a bill of materials concept but i haven't figure out how to handle that one yet.

Any feedback or suggestion would greatly appreciated.

Thanks again for all the help.

 

[gemma-the-husky]

normally raw materials get booked out on to a works order, then get rebooked in to stock in whatever format they have changed to - it wouldnt be normal to track stock itself in produiction different locations -- you just trace all the items used via the works order - so your stock consists of stock STILL indicated as being in a storage location, together with all WIP - ie uncomleted works orders.

if you actually have different STORAGE locations for the stock though, thats different. In thatr case, your stock file needs to include a location - and then if you move stuff from A to B, you do NEED a transfer posting (ie 2 entries) to remove stock from location A, and increment the stock in location B.

i'm sure you are aware, but managing stock is always a very complex area - the exact procedure you use will depend on your specific industry/business


------
so in general your structure is not what would normally be used to manage stock - normally, the product table just stores product details - the stock quantity should be calculated by adding all stock movements

so your tables ought to be something like

products (id, description, and in a full blown system loads of stuff about reordering statistics, default supplier etc etc)

locations (locationid, description etc)

stocktransaction (productid, locationid, tranactiontype, date, quantity, worksorderid)

stocktransactiontypes(sttype, description) (ie intake, sales, adjustment, etc)

worksorder( this will depend on your business, as it may be sufficient just to have a header, linked to stock transactions,
or the worksorder structure may need ot be more complex - as the work moves around the factory some indicator on this will most probably identify exactly where it is - ie which machine etc - while stuff is on the shop floor it is WIP, and most probably not in stock as such - it goes back into stock on completion of the works order - this is far easier than trying to move stock form machine to machine)

---------
this deals with stock quantities - if you are trying to manage prices as well, then it is inordinately more complex, as you will need an issuing mechanism (lifo, fifo, avco, standard) and probably some way of calculatiing various production variances.

----------
one other thing that is often common in stock systems is "packages" of stuff - eg in a bathroom supplier, the bathromm could consist of a bath, a toilet, bath panels, and assorted ironmongery that goes with it - so you can bookout one bathroom suite, but what is ACTUALLY booked out is the individual items forming the package - which is OK until you have some items not in stock, and then you have to decide how you going to deal with partial orders, backorders and so on. - its all things like this that make stock so complex

 

[Thinh]

Thank you clarify the concept. I think i can make it work the only thing i haven't got to establish is how do i handle if a transaction needs to be approved first?
Some task require 2 person while other only required one person. inflow requires one person that receives it while issue a work order materials to get cut requires two person one that cut and one that approves it. i have added the employeeID into the stocktrans table because one person will always be require for each transaction. I figure the approved person would be in the work-order table somehow. Another thought is to create a temp table that holds all the cut pieces before it actually gets approve and posted to the stock trans.
I am not sure if i got the correct idea. I have one more question how do i handle work order that require cutting pieces of certain length. the total length will be pieces *Length. The problem that i face is work order could have many different options so it looks like i may need different table for work order unless i want to be non normalized.

Any suggestion is greatly appreciated.

 

[gemma-the-husky]

thinh

i don't really understand exactly (in full detail) what you are trying to achieve with your system.

all i would say is to repeat that you are getting into IMMENSELY complex areas, if you are trying to build a full stock management system to include works orders etc etc. This will take many man-weeks, not to say man-months of effort. You need to get your data structures right at the outset, and you need to really understand all the little nuances of your industry/business, and also design a system that has the flexibility to deal with different control parameters subsequently.