Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Design question-Inventory items that split

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    So, where are the 52 pills?
    Are they rolling around loose in the bin, or are they also in a bottle?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dpearceMN
    I had the idea of converting an inventory item: Using the above example, I would remove one bottle from inventory and add 100 pills.
    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.

  5. #5
    Join Date
    Dec 2008
    Posts
    2

    Smile Thanks!

    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.

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jan 2009
    Posts
    1

    do you really need an ID?

    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?

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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...)
    Last edited by loquin; 01-12-09 at 17:35.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •