Re: tracking shipments, inventory

What I meant by "temporal dependemcy is burdensome and worrisome" is this:

Let's say I have 2 separate shipments to stock of 100 pieces each. Then I have a shipment from stock to customer of 150 pieces. And then a final shipment from stock to customer of 50 pieces. The delivery is complete and there's nothing left in stock. Great. But those latter shipments to customer depend upon the former shipments to stock in a temporal sense that seems impossible to enforce with typical DBMS constraints. In other words, negative stock quantities are entirely possible in this database design but impossible in the real world (putting aside the issue of backorders). Sure I can add logic in procs and triggers to prevent a negative stock situation.... but that's the burdensome and worrisome part. WHAT IF there's a hole in that logic? How do I make bombproof rules to enforce these sorts of temporal constraints?

Thinking aloud, I suppose I could FK the customer shipments back to the stock shipments and constrain the shipqty......? Received on Fri Dec 24 2004 - 06:39:43 CET