I have an e-commerce project coming up in the next quarter and I want to just piggyback (not hijack the thread) one question onto this thread. And that is, don't e-commerce packages come with the functionality to track inventory? I mean, most times when I shop online, I see whether an item is in stock or how many pieces a retailer has in stock. I am concerned that there is more work than meets the eye with an e-commerce implementation in regards to constructing a DBMS to handle the inventory issues in addition to implementing the shopping cart and the catalog. Sorry if this is off topic, but this issue just raised a red flag for me and I need to understand it a bit better before I put together an estimate. I have a catalog component and a credit card processing component at my disposal, but need to know if it is common practice to hand-build the inventory management end of it. Consequently, this could help with Aaron's original question if there are inventory management components out there already by allowing him (and I) to simply use an existing one rather than reinventing the wheel, so to speak. Thanks, Rich > -----Original Message----- > From: Matt [mailto:hj611@xxxxxxxxxx] > Sent: Friday, May 21, 2004 11:42 AM > To: Aaron Wolski; php-db@xxxxxxxxxxxxx > Subject: Re: Inventory Management for ecommerce store > > > > 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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
