>>1. Do we keep updating the table the moemnt someone >>enters a item in the shopping cart?, OR
Quantity will never be correct, ie quantity is 5, you have 5 buyers, and each has 1 item in their cart. So the 6th buyer cannot purchase your product. Now 3 buyers walk off, or leave your site. Your inventory is bougus and shows no items.
>>2. do we update when check out process is complete and >>custonmer has actually bought the product and cedit card >>goes through?
Inventory is 5, and 5 buyers each purchase 5 items. Buyer 1 completes the sale, and now buyer 2, and you have 0 inventory.
You have to have a way to lock, and keep track of pending items. Either keep a actual quantity and pending quantity of which total (actual - pending) is used for the buyer trying to add to his cart. Cleanup will be necessary for those you lose connection/ or abandon your
End of Day process would update pending to actual.
I use the transaction file to get my pending, with a simple SQL query, to get quantity on hand. A simple task runs from time to time and moves those transactions to a abandoned table when they become so many minutes old, which is a tunable parm.
AT reconciliation, the transactions move to the invoicing and actually update the quantity on hand.