Inventory DB

[CEH]

I've been working on a new project, an Inventory DB..... Everything works well... BUT....... Now I am back to a design issue so I can deal with a FIFO inventory. I have searched the forum and every place else to try to see the prefered method for setting this up..... Can't find much. I have seen using serial numbers, or dates. Neither would work well for what I am looking at. I have thought of using a ID field for Inventory purchased.... Auto number... This would then show the first items in as the lowest number.
But........ What is the common method for setting this up? Any examples around?
Thanks

 

[ByteMyzer]

Ooh, FIFO, my faaaaaavorite creature

A basic FIFO inventory structure should have information similar to the following:
* a batch or lot # (automatically assigned)
* the date the batch or lot was created in the database
* the part/item number
* the quantity
* the standard unit cost at the time the batch or lot was created

Example: Item # 12345 was received into inventory for a quantity of 5 on 5/1/2006 at $5.00 each, and a quantity of 7 on 6/2/2006 at $5.75 each. The resulting data would be something like this:

Batch # | Date       | Item # | Qty | Cost
--------------------------------------------
0000001 | 2006-05-01 | 12345  |   5 | $ 5.00
0000007 | 2006-06-02 | 12345  |   7 | $ 5.75

When selecting inventory to ship/issue, your application should select from the earliest available dated batch for the Item #. For the data in the above example, if you were to place an order for Item # 12345, for a quantity of six, the application should first select the five pieces from Batch # 0000001 at $5.00 each, then the remaining one piece from Batch # 0000007 at $5.75. The resulting COGS (Cost Of Goods Sold) for your order for Item # 12345 would be $30.75.

This is a very basic example of the FIFO inventory concept. Inventory valuation methods in database design are definitely not for the faint of heart, but I hope that this gives you a good starting point in your efforts.

 

[CEH]

Thanks Byte........
Good explanation for FIFO... But...... I have no problem with inventory valuations.. (been in accounting for 20 years) My problem was getting it to work in the DB..... I assumed something like a lot# or batch# on each purchase. I already have all the other fields. And btw... I am using one field for ItemIN (quantity) and ItemOut (quantity).... Now my REAL problem probably requires a solution in coding..... Purchased inventory gets a lot# increased by one with each new record... But the sale is a trick........ How do I loop it thru each batch or lot? "Take 5 items from lot# 000001 then 1 item from lot# 000007" leaving me a remaining inventory of 6 items in lot# 000007 @ $5.75 cost per item.

 

[anchorp]

Any more info?

Did you ever get this figured out? I am facing the same problem!

Thanks,