>>This should tell you "which item contains which other item", right? Since this is a many-to-many relationship, you will need a separate table, which you might call ItemComponents, or ItemComposition, or something similar.
>>
>>Minimum fields:
>>
>>Parent (an item)
>>Child (an item contained in Parent)
>>Quantity
>
>Actually we are talking about the same thing in diff. lang. <g>

Yes, I am still trying to understand what each table is supposed to hold.

> We call it Bill Of Material (BOM) here. For item heirarchy I have iParentsID in the Items Master itself. This Items Master is supposed to maintain the hierarchy by having iParentsID and another table BOM Master will again call Items Master for item codes as iItemID and it's qty.

I am not sure about the terms used. But since a) each item can contain several materials, and b) each material is contained in several items, you can't save this information in items or in materials, and you need a separate table (to solve the many-to-many relation).

Do you need a hierarchy ("A" contains "B", "B" contains "C", etc., perhaps for several levels), or do you want to consider this for future expansion?

>>>- Multiple Units qty.
>>
>>Can't you convert to a single unit?
>
>Yes they are finally converted to the main unit, but stocks are supposed to be maintained seperately also. I had thought about this as item heriarchy using it directly into the Items Master but it will so happen that the user will want to know about all item stocks for items hainvg say a packing of 1 kgs. So I thought a seperate DBF child to Items Master might do the job.

OK: This will require a table, where you have the quantities in stock, for different units. You may also want to have a table where you have the totals - for instance, for one particular item, everything converted to kg.

>>
>>>- BOM
>>
>>What does this TLA mean?
>
>see above
>
>>
>>>- Godown-wise stock
>>
>>Can you explain what this is about?
>
>Love to. This unit has a total stock say 500 units of those 100 units are at a suburb unit and 300 at another and so on. Also to complete the whole specs, out of these stock of 500 there are packing of them say in 1 set, 5 sets and 10 sets packs (again distributed in proportion to then units lying with each suburb units) thus the Secondary Units master. As each set has a BOM thus the BOM Master.

So you want to keep track of stock in different locations.

In this case, your detailed stock table simply has one record for each combination of item, unit and location:

Item      Location       Unit     Quantity
Sugar     Main Warehouse  1 kg.        100
Sugar     Main Warehouse  10 kg.        30
Sugar     Suburbs         1 kg.         35
...

You may consider summary tables, which combine stock for all warehouses, for all units (converting to a single unit) or both, but you can also do this by reports only (do the summary calculations when they are requested).

Saludos,

Hilmar.