Hi Bhav

>I have generally handle inventory systems so I am generally aware of the basic theories. Now I have to plan for a Inventory database for a Pharma company and the items have the following specs.:
>
>- Each item has it own stock (obviously :)). For eg. Liters
>- Each item has it's stock distributed in various packing sizes. For eg. in bottles of 50ml, 100ml and 500ml
>- Each item has the above stock further distributed into Batch Numbers
>
>Making them create as a new item (new internal unique id) for each and every packing size may raise complaints and asking anything similar for Batch Number and I am sure to loose the contract.

You can keep packing sizes and batch numbers as separate lookup tables.
Item can have single id , while packing size and batch no, will be recorded only in movement documents themselves
(purchase orders, delivery sleeps etc) and consecutively generate item movement tables.

I would keep separate item movement table, where each transaction (document) is recorded in its very basic
unit of measurement (mililiters or miligrams) as one or more records, simillar to debits and credis in accounting.

Then you can calculate item quantity balances in a simillar way you would reach an account balance.
Starting from some openning qty balance + input - output = qty balance as at certain date.

Stock control is infact simillar to accounting, just that it is not the accounts and values but rather
items and quantities that you are accounting for.

Bid with confidence ;)

HTH
Sergio