Tutorial or help with Advanced Transactional Inventory Database Design?

Question

I'm working on a transactional inventory system for our e-commerce company, and I'm looking for some sort of a guide or tutorial on how to accomplish this with a MySQL database. There's guides on how to program, and guides on how to USE such systems, but I've been unable to locate a resource with suggested table structures or best practices for implementing your own.

In this system, all of the items are bought, stored, resold, then shipped (no manufacturing). The system should support multiple "Locations" (ie. Wisconsin, North Carolina). Each Location can have one or more "Warehouses" (buildings next to each other). Each warehouse can have one or more "Isles", with each Isle having one or more "Shelves", and each shelf having one or more "Bins". Merchandise is stored in bins, and each size/color variation has it's own bin. Some items may be stored in more than one bin (ie. if we get a discount by ordering 2000 at a time, we might stock 10 in a low bin and put the rest up on a high shelf in "overstock".

Storing items in their respective locations is simple enough. I would probably have a Bins table like:

BinID     BinName            LocationID     WarehouseID    IsleID     ShelfID
---------------------------------------------------------------------------------
1         Widget Bin A          1               1            1           1
2         Widget Bin B          1               2            2           5
3         Large Widget Rack     1               1            5           17
4         Widget Overstock      2               3            6           23

And then move items between Bins with an Inventory Transaction Table Like:

TransID   SourceBinID     DestBinID     QTY       Date      Memo
---------------------------------------------------------------------------------
   1          4              1          10     7-22-2011    Moved 10 Widgets...

But here's where I get confused:

Let's say that it's midnight, and a customer orders 2 Widgets from the website. There's 5 widgets in a bin. The customer has paid for his 2 widgets, so no one else can buy them, but it's midnight and no one is working, so his widgets are still sitting in the bin. I need some sort of transaction that decreases the "sellable" number of widgets without decreasing the number actually on the shelf.

The next day, an employee picks those widgets and moves them to the packing area. Now a physical transaction needs to occur to note that the items are now on a packing line and no longer in their bin. But packing needs to be a special location, since it's not the same as a regular "Bin", right? So somehow we need to move things from a bin to a special non-bin location, and we need a transaction entry for doing that.

Then there's other "special" places like a return quarantine, and a place for setting aside items when other items for that order are on backorder.

If you know of a book or online resource that can explain how to do this from a programming/database standpoint, that would be great. Or if someone already knows how to do this and would be willing to share, that would be great too!

Thanks!

UPDATE:

I've been giving this some more thought and it's possible that the "sellable" inventory (I think it's called "on hand") could be calculated dynamically. "Total of all bins" - "unfilled orders" = "on hand". The question is, is that going to be too slow to be practical? It would need to perform several queries and then loop over the results to get the on-hand total.

The alternative might be to have a separate transaction table for "on-hand", but then you have two inventory journals- an "on hand" and a "physical". Even though they should always stay in sync (if not it's a bug!) it still doesn't feel right?

I'm still not sure what to do about physical places. When you ship it, it's gone from inventory, but for double entry style accounting it needs to go somewhere. So I would need some sort of "gone" bin. But it doesn't feel right to make "gone" and "packing table" "bins" either, because they aren't actually bins.

UPDATE 3

Movements:

MoveID     TransID      SourceBinID     DestBinID      Memo
---------------------------------------------------------------------------------
   1          1              4              1          Moved 10 Widgets to bin 1
   2          2              1              4          Received 10 Widgets from bin 4

Issue:

IssueID     TransID      SourceBinID        Memo
---------------------------------------------------------------------------------
   1          3              4              Shipped Widget to Customer
   2          4              1              Shipped Widget to Customer

Transactions:

TransID          ItemID            Date         QTY     Type  
---------------------------------------------------------------------------------
   1               1             7-22-2011       10     Move     
   2               1             7-22-2011      -10     Move
   3               1             7-23-2011       1      Issue
   4               1             7-24-2011       2      Issue

UPDATE 4

Ok, I'm going to take another stab at this, without allocations for now.

Locations Table - A "Location" is a place where stuff can physically “be”.

       
LocationID  LocationTypeID          LocationName
-------------------------------------------------------------   
1                   1                   A Widget Bin    
2                   1                   A Widget Bin
3                   1                   A Widget Bin
4                   1                   A Widget Bin
5                   5                   Vendor (nowhere)
6                   3                   Packing Table 1
7                   4                   Gone (shipped to customer)

A location has a "type". A location can be a bin, an overstock location, a Packing Table, or represent a customer (for outbound) or a vendor (for inbound).

Location Types

   
LocationTypeID  LocationTypeName
-------------------------------------------------------------
1                   Picking Bin
2                   Overstock Bin
3                   Packing/Shipping Table
4                   Shipped Items
5                   Vendor (for reviving)

Physical Transactions - When things move around.

               
TransID   LocationID   Debit      Credit   Memo
-------------------------------------------------------------               
1          5             10                 Initial purchase of 10 Red Widgets
2          1                      10        Initial purchase of 10 Red Widgets
3          1             2                  Pick 2 Widgets AND....
4          3                      2         Move them to the packing table
5          3             2                  Ship Widgets to Customers
6          4                      2         Customer Gets Widgets

Answer 1

The term that is most commonly used is "allocated". You have 5 widgets on hand and 2 have been allocated. That leaves 3 "available". When those items get picked you need to cancel that allocation.

When you remove items from inventory (I call that "issuing"), you need to provide some kind of "cost account" that allows you to identify where that value has gone.

The other piece of advice that I would have is to do an inventory movement using two transactions. Remove quantity at one location and add it to another. By doing this you can keep all the transactions that affect on hand quantity in a single table and it makes determining the on hand quantity on the fly pretty easy.

What I do looks like this:

This allows me to keep more detailed information about the particular type of transaction in the separate tables and the common stuff in the main transaction table.