| If I understand your description...
I would not recommend keeping a "number in stock" table and deleting from it when an order becomes pending... You would do better to create a query-table on a regular interval which had the number available based on "inbound", "arrived, "pending", and "completed" tables. Use a query to the "pending" table to subtract from the "inventory" table.
Alternaely, you can actually move the items from one table to another. When an order is placed, don't just remove it from inventory; move it to a "pending order" table. Then don't delete the order, cancel it, and move it from the "pening order" table back to inventory.
There are several ways to accomplish all this... from a purely business standpoint, the preference is a master record and query tables for the status's... from a project standpoint, you can probibly just create the 4 tables yourself. |