No problem on the piggy-back. I do actually have an Inventory Management component in place but it seems to be acting up a little bit here any there. This is what I do: 1) For each product; create an Inventory Record for the product SKU, product_id AND attributes 2) I have 2 "inventory" fields that hold the stock info - quantity and approvedQuantity 3) When an item is added to the cart; I subtract the item quantity from the inventory quantity that is available 4) If an item(s) is removed from the cart; the quantity of the item(s) is returned back to its' Inventory record(s) 5) If an order is canceled; the quantity of the item(s) is returned back to its' Inventory record(s) 6) If the customer processes their order; the approvedQuantity for each items' Inventory Record is subtracted by the quantity being ordered In the end, both the quantity and approvedQuantity for an Inventory record should match. This is where I am having a problem and I don't know how to track/fix it. I HAVE Inventory Records where the approvedQuantity and the quantity are NOT matching. I asked the question to see what others might be doing because it seems how I have it setup is obviously not working :( Can ANYONE see where my logic is flawed? Thanks! Aaron > -----Original Message----- > From: Hutchins, Richard [mailto:Richard.Hutchins@xxxxxxxxxxxxxx] > Sent: May 21, 2004 11:49 AM > To: php-db@xxxxxxxxxxxxx > Subject: RE: Inventory Management for ecommerce store > > 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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
