Maintaining Stock Availibility : physical table or materialized view ?

Hi All,
We have typical Order processing application where I should maintain stock availibility => Order is rejected if Stock is not available. The number of orders is 1000 perday, 22 branch, using VPD. 10 users each branch.

We are really tempted to use JUST view /materialized view to maintain stock position.
(where traditionally we use a phiysical table for this, updated after insert/update/delete of transactions)

So the view will be like this :

Create View Stock AS
Select Prod.Code, Prod.beginning_balance,

( SELECT Sum (Qty) from Purchase Where ProdCode = prod.code
...) AS Purchase,

( SELECT Sum (Qty) from Order Where ProdCode = prod.code ...)
AS Order,

         ........
         From Products Prod

So to get the stock availability for a particular product : Select Beginning_balance + Purchase - Order from Stock Where Code = ...

Please give comments, will this work with good performance ? Or has anybody done like this in their application ?

Thank you for your help,
xtanto Received on Wed Sep 06 2006 - 19:10:08 CDT