"Real Time On Hand" design hints? (long)

120ish lines coming up...so ya know. I've been fighting with this thing for the past couple of years, but we may get a chance to re-do some things coming up, so I'm looking for other avenues. Platform: Informix Online 5.x, 4GL 4.1x Everyone runs into this problem, sort of, but I'm looking to see how others handle it. What the users want is "Right Now" inventory availability. They'd be even more giggley if the numbers are actually correct :-). In one sense, it is your typical inventory system: quantity on hand and quantity not shipped yet. Qty available is on_hand - not_shipped. We managed to complicate matters. On hand qty is based upon raw material. Raw material is sent to production centers where it is then converted into finished product and shipped to the client. Client gets to pick the production center, we get to pick the raw material origin. Raw material can originate from several locations for one client order from one production center. We have three distinct datasets maintained in the system. Raw material status, raw material allocation, client orders. Raw material status says at raw material location R for item I, we have 20 on hand, and 10 committed. Allocations says we have 10 item I committed from location R to 4 for production location P1 and 6 for production location P2. We have 3 orders, one shipping 3 I from P1, one shipping 1 I from P1, and one shippping 6 I from P2. Raw material knows the demands placed for each item/location, but not where the material will go. Orders know how much it wants to ship from each production location, but not where the raw material is sourced. Allocations is the middle man, summarizing the whole thing. This is the model we use, simplified, and it's a good model, I don't see this changing. We have a bunch of stuff during an order save to spread out the allocations automagically when an order is saved, so that we don't over-commit a raw material warehouse, however, a CBL (carbon based lifeform) can come in later, and effectively move the allocations around manually to suit whatever whim he's trying to suit. We want to have "real time" availability numbers presented to the customer service reps during order entry. Currently, I'm pulling this off by having a "commit" table showing order intentions during the actual order entry process, so that I don't have to update the raw material records directly, except during order save. So: qty_avail = sum(on_hand raw material in all warehouses) - sum(committed raw material in all warehouses) - sum(qty currently being promised by all reps) + qty this rep has already promised. OE is all done in a transaction, so Locks Are Bad. Intent in current orders is handled in a special table with no indexes showing what everyone has already promised. The reps will put in order detail, and then hit a key to "commit" the lines, which locks their intent by putting the detail into this commit table. At order save, all of this stuff gets wadded up, summarized and forced into the allocations tables, and then into the raw material tables. "Contention" is the dirty word here. The big problem is that I see this whole system being in one big, confused race condition, and we're wanting to add some functionality to the commit logic that's going to make things worse. Also right now, for some reason the Allocations get out of balance. I've tested this code 'til Tuesday, and, individually, it works just ducky. I'm guessing my problems are happening because of stale data when two orders are committing simultaneously, but I'm not sure. I had several little land mines in the applications, and in triggers to try and track the out of balance problem, but all I ever found were footprints in the sand, but no leprechauns. I think I've tried setting to a draconian ISOLATION level, but then contention just exploded. It's easier to fix the out of balance allocations than to deal with stopped, stuck and exploding OE programs. But, because of new stuff wanting to be done to the system, this current "fix it later" technology won't really work. Right now I'm contemplating centralizing the access to the Allocation and Raw Material information through a central "Commit" server. This thing would handle, and serialize, all of the transactions affecting availability, and allocations/raw material. I see this as a bottle neck (with a capital B), particularly if I include an availability service as part of the processing. (OE asks the server for all of its numbers). In another sense I'm being redundant and re-inventing the wheel, and the DBMS should handle a lot of this stuff for me. However, this central commit server will give me the benefit of a better global view of information, and will enable us to make better "strategic" availability decisions (such as putting off the ship date a week). I fear it will all be a folly, though, but it's hard to tell until its done. And once its done, it'll be a bitch to go back. So, I'm looking for other ideas on how to help mitigate the contention issues involved with processing of this type. How are you folks all handling the issue? Getting a faster machine/code can relieve the problem, but doesn't necesarily solve it. Another thought is to just maintain a detail history log of changes to the allocations and inventory during the day, and rolling it all up at night, but this complicates a lot of programs and, especially, reports that are run during the day. But, perhaps it is doable. Sorry for the length, and I know this is hardly an isolated problem, but I wanted to give some depth besides "how do you solve the contention problem". Thanx for any thoughts. -- Will Hartung - Rancho Santa Margarita. It's a dry heat. vfr750@netcom.com 1990 VFR750 - VFR=Very Red "Ho, HaHa, Dodge, Parry, Spin, HA! THRUST!" 1993 Explorer - Cage? Hell, it's a prison. -D. Duck