Sorry for the length of this question.
I have a section of our database design that I worry can start to cause problems. This is not yet at that stage, but, obviously, you should not wait until he solves the problem. But before I start testing various scenarios, I would be grateful to everyone who has experience with such a problem.
The situation is inventory management and maintaining the value of StockOnHand.
One could maintain a table holding stock control digits, which can be updated whenever an order is entered manually or using a database trigger. Alternatively, you can get SQL to calculate the values by reading and summing the actual sales values.
The program is installed on several sites, some of which use MS-SQL 2005 and some 2008.
My problem is complicated, because the same design has to cope with several scenarios, for example:
1) Cash / sale. A sale was introduced, and shares were reduced in one transaction. No amendments may be made to this transaction.
2) Order / Routing / Confirmation In this environment, an order is created and can be placed on hold, issued, redirected, changed, delivered and invoiced. And at any stage, until it is billed, the order can be changed. (I mention this because any database triggers can be triggered a lot of time and must determine if changes should affect the numbers in stock)
3) , StockOnHand . , , ( , , ). , , , .
4) . , , , SQL , , .
5) . .
6) , ( , , ).
, . ( , ).
7) . . , , , , - newAverageCostPrice = (((oldAverageCostPrice * oldStockOnHand) + newCostValue)/newStockOnHand). , On Hand , AverageCost.
.
, StockOnHand . , , - (#)
.
, . , , .
, .
( ), . ( , , , , )
, StockOnHand, .
. , . , 90% 4 5 , , , .
.
, .
, , , , . . , , . .
. ( , , , , .
StockOnHand , ( , ) , -invoiced , .
.