> From: "Aaron Wolski" <aaronjw@xxxxxxxxxxxxx> Friday, May 21, 2004 10:52 AM > Subject: Inventory Management for ecommerce store > I'm looking for some advise/login on developing an Inventory Management > system for an ecommerce store and what pitfalls one needs to be on the > lookout for. You should have at least two ways to indentify each stock item, a unique item number (auto increment) and a displayed value (catalog number). All of the inventory records are always keyed by the unique item number. This allows one to change catalog numbers easily without effecting keys. You may want more fields such as manufacturers item number, and UPC code. As for inventory counts, here's what I do. I have 3 fields: quantity-on-hand, quantity-committed-to-orders, quantity-sold quantity available is calculated: quantity-available = quantity-on-hand - quantity-committed-to-orders When an order is created, the quantity-committed-to-orders is incremented by the order-quantity. When an order shipped, the quantity-committed-to-orders, and the quantity-on-hand are decremented by the order quantity. The quantity sold is incremented. If an order is canceled, decrement the quanity-committed-to-orders. In this way, you always know what the quantity in stock is, and you know how much of that stock is allocated to orders. I also recommend an item ledger table that includes item number (unique), catalog number, trans type, quantity, order number, selling $, cost, who, when, why. Each transaction (order detail at ship, receipt of stock) is logged to the item ledger. The item ledger provides an audit trail for accounting and for you to answer question on what happened to inventory. Inventory adjustments (losses/gains) from physical inventory go there too. Order Lines should have the unique id, catalog number, qty, sale price, cost, actual tax (if taxed at this level). --- The future will be better tomorrow --- http://www.spiceplace.com/ ---------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
