> User1 starts order and takes the last two units. User2 starts order 1 minut=
> e
> after and checks inventory. He sees 2 units left and adds them to the his
> order. User1 commits his order. Now User2 cannot finish his order because
> the products are not available anymore.
>
> This is the problem I want to avoid. Therefore if User1 takes the product
> but does not finish the order I want the inventory to still show that the
> product is sold out to other users.
>
> Any suggestions on how to implemnt that?
One common way to deal with it is to have a separate 'hold quantity'
field (or table) for items in pending orders. You can commit to that
field or table as each line item is entered, revised or deleted during
order entry. When the order is finalized, you simultaneously release
the hold and take the item out of inventory.
The primary problem with this method is abandoned orders, because you
want to release that inventory so someone else can order it.
That's more of an issue if you are writing an application for your
customers than if it's being used by a sales staff who will know to
cancel an abandoned order. (However, you probably still need a 'cancel
pending transaction' capability to deal with things like system crashes.)
I once designed a web-based transaction system which kept a timestamp on
each 'on hold' line item. It assumed that if the order wasn't completed
within an hour the order had been abandoned, and at that point it released
the hold on any items. (Actually it just checked the timestamp when
adding up the 'on hold' quantity during an inventory check and ignored
any timestamp that was more than an hour old.)
There was also a one hour inactivity timeout on the web form, as I recall.
You should be able to do most of this with trigger functions.
--
Mike Nolan
