Hi all

I have this requirement for an inventory system and the following is the rough master data structure I have in mind:

Items Master where iParentItemsID is for maintaining a tree structure of item heirarchcy. Item in say nos., having no secondary units, can be purchased or sold on say kgs. thus the yItemWeight

iID
iParentItemsID
cDesc
yOpStock
yItemWeight

Secondary Units Master to maintain breakup of the Items Opening Stock in say for eg. packs; 10gms Pack, 50gms Pack, 1kg Pack. cConversion is / or * to convert it back to the Main Items Stock

iID
iItemMastersID
cSecondaryUnit
yOpStock
yConversionQty
cConversion

BOM Master this will maintain Bill of Material for each item. iItemID uses the Items Master above. cAddLess is required as in a production there is less of the actual BOM items but add of Scrap etc.

iID
iItemMastersID
iItemID
yQty
cAddLess

Now there is stil one requirement is Godown-wise stocks primary (which is a club of secondary units) and secondary units. This was still not clear how to implement as there are 2 places where there is a opening stock qty.

In short I need:
- Item Heirarchy
- Multiple Units qty.
- BOM
- Godown-wise stock
- and whatever the client will require <g> on the fly <bg>.

Any suggestions.