I have been asked to write an inventory system for medications for a clinic in a third-world country.

I have my head wrapped around what they need except for one area: As an example, pills are received as "one bottle of 100 pills" and need to remain in the DB as "one bottle" until that bottle is moved to the pharmacy itself when it needs to change to "100 pills".

So at any time, the same item might have 4 bottles plus 52 pills in inventory.

I had the idea of converting an inventory item: Using the above example, I would remove one bottle from inventory and add 100 pills. This seems clunky.

Any better ideas?

So, where are the 52 pills?
Are they rolling around loose in the bin, or are they also in a bottle?

The method you are planning (where you remove a bottle from inventory and replace it with 100 pills) is the only one I know is accepted by regulatory agencies. Only a few countries have really serioius auditing requirements for pharmaceuticals and I wouldn't consider any of them "third world", but it would be nice to both be prepared and to use standards that donors would easily understand.

While it might seem "clunky" to break down inventory from pallet to case to box to bottle to dose, that is how every successful system I've seen works.

-PatP

It sounds like you intend to add 100 records representing 100 pills - I'm sure that's not what you mean but could you clarify. Why not just have a field representing how many pills are in the current bottle and then another field to show how many are left (or how many have been taken) which will be null if the bottle is unopened. This could obviously be made more generic by adding a type field so you could offer a similar functionality to a wider range of items ie boxes of bill bottles or pallets of boxes etc.

Thanks guys/gals for your help.

blindman - The 52 pills would either be in an opened bottle or put into individual bags.

mike_bike_kite - Actually I would go from one record describing quantity one bottle of 100 pills to one record of of quantity 100 pills.

Pat Phelan - Thanks for the encouragement. I do want to use standards. Since this clinic receives meds from multiple countries (all of which are first-world) and many now require paperwork they used to not require, going with something "normal" makes a lot of sense.

Thanks again.

Multiple units of measure allowed for the same item...

Our inventory system has a units conversion table, column0 is the ID, column2 is the FROM UOM, column2 is the TO UOM, column 3 is the Conversion factor.

Entries for your application might be

17, 'BTL100', 'EA', 100
18, 'EA', 'BTL100'. 0.01

When cracking a new bottle, you would decrement the bottles record quantity by one, create a new record (or modify an existing record) of pills by incrementing it by number of bottles decremented multiplied by the conversion factor from the unitsconversion table where the From Units and To Units match the bottles record and the units record.

Do you really need column 0 to be an ID, or could you simply use the combination of the FROM and TO columns to uniquely identify the conversion?

It certainly could be a compound PK.

The PK of the inventory records consists of item_number, UOM, and location. So, you can have two (or more) records for a given item and location - up to one for each applicable unit of measure.

maybe...
2 cases of aspirin, each containing 100 bottles
17 bottles of aspirin, each containing 200 pills
and
37 pills of aspirin
...

Changing an inventory location record from one unit of measure to another (splitting) is just another type of inventory transaction. The 'from' and 'to' locations might be the same, (although they wouldn't have to be...)