How do most folks handle inventory control. i.e. You have six widgets in stock. You sell one, now reduce widgets in stock to five.
I have done this at the transaction level for a project that had relatively few, but very large, widgets with typically one or two widgets per order and only a handful of transaction per day by scripting the reduction of quantity as each sale was processed.
The current project I am working on has many types of widgets with many widgets to an order, in a retail type enviroment with multiple people processing orders at the same time.
I fear adjusting inventory as the transactions were processed using a script would slow down the transaction process under load. I could see doing this in periodic batches but fear selling more widgets than we have on hand at some point. Or would this be best handled by using a relationship?