Efficient Way to Maintain Kitting/Assembly/Finished Good Inventory Product into Database

Well, let me share with you what I have in my ERP System.

I will only show you important columns, not all within the tables

Tables to handle this:

Inventory:
Item_Code      nvarchar(50)
Item_Description      nvarchar(80)
Item_Group      nvarchar(30)
Item_Type      nvarchar(60)
Item_Segment      nvarchar(60)
Item_Area      nvarchar(60)
Category      nvarchar(60)
UOM      nvarchar(3)
-----------------------------------------------------------------
The above inventory_type will identify whether this item is a "Raw Material" or "Finished Goods".
"Finished Goods" means completed item.

"Raw Materials" are for kitting and assembling.

------------------------------------------------------------------
Then I have something called "BOM" header and details table, these tables keep information
about how an "Finished Goods" is kitted or assembled.

BOM Header:

BOM_Code      nvarchar(30)
BOM_Description      nvarchar(100)
Assemble_Or_Disassemble      nvarchar(15)
Item_Code      nvarchar(50)
Item_Description      nvarchar(100)
Repacking_Cost_PU      smallmoney
Labour_Cost_PU      smallmoney
Loss_On_Production      numeric(6, 2)

BOM Details
BOM_Code      nvarchar(30)
RM_Code      nvarchar(50)
RM_Description      nvarchar(100)
Quantity      numeric(18, 4)
Normal_Loss_Percent      numeric(6, 2)

The Header keeps information about the "Finished Goods" and the Detail table keeps information
about all related inventory items that make up the "Finished Goods"

--------------------------------------------------------------------------------------------------
Then I have assembly transaction tables, header and details keeping information when
kitting or assembling transactions are transacted. Once transacted, I will calculate the used
"items" and deduct them from the "Inventory Movement" Table, which in your case maybe
call "Quantity" table, which I don't recommend.


------------------------------------------------------------------------------------------------------
So means to say all these items are also within your inventory, they are "Raw Materials". Of course,
you can also sell them without assembling since they are also your inventory items.

   - 250GB Hard Disk
    - 1.44 Floopy Drive
    - Core 2 Duo 2.0 processor
    - 4GB Ram
    - 19" LCD Samsung Monitor
    - Standard Keyboard
    - Optical Mouse

So for this example, you have the individual items above and have one "Finished Goods" call "Clone PC".

So in the BOM tables, you keep information that one Clone PC is make up from each of the items above.

----------------------------------------------------------------------

When users transact a kitting or assembling, ask the user quantity to kit/assemble, then once confirmed,
minus the related raw materials which are your Hard drives, LCD etc and plus the "Finished Goods" , Clone PC to your inventory balances.

Hope it helps.