I treat change orders as new orders entirely. since order lines would be what affects my inventory and billing at a summary level, everything balances in the end and locking can be handled "for sure" this way.
You could perhaps come up with your own custom client-facing order numbering system that allows you to have Order #1000 and #1000-1, but keep your primary keys hidden as values 1000 and 1001 on the backside for your relationships.
Hi Mike, thank you for offering advice. I didn't want to cloud the issue with my idea of design. I was thinking a new table change_order (or perhaps use my existing order table and add a type?) that had the fk_order_id and would use the same order_item table. Each order_item would have an fk_order_id and fk_change_order_id. All keys are UUID. Yes, I have an auto-enter serial for client-facing.
So, hmm, if a qty were to decrease for an item, you'd cancel the order_item on the original order and have client create a new order with the corrected amount?
Edit: You're not saying that at all are you? Just create a new change order and if I were to decrease an item, this order_item would have a negative quantity. Correct?
Do the orders involve handling of inventory?
It heavily depends on what the scenario, business type / model is.
Anyway, once an order is past a certain status. No modification allowed.
Create a change order, either empty or dupe the original so you can add / delete / change quantities.
Yo'd probably need a sum total of change orders on your original order to see the balance.
Put a reference to the original order, maybe in a field called original_order_nr or reference.
I'd also use a field
When a enduser want to change a record later in a process, even if difficult to program, i first will try to see if it is possible safely.
I see two main caveat to allowing changes on a confirmed order :
- History loss of a particular business
- Corruption of the coherence with other records and process (for instance in a fabrication process)
If possible, i would after each change, generate automatically a PDF to store the business's history on a related and timestamped table and thus, solving #1.
Additionally, i would try to create a script that update all related records accordingly if possible, in particular if allowed by accounting.
If not possible for whatever reason, i would do as electonsaid :
"once an order is past a certain status. No modification allowed."
And then, as Mikesaid :
"I treat change orders as new orders entirely. "
The part that is the most difficult for me is that each order_line relates to:
- one or many po_line_item
- one or many invoice_line_items
- one or many shipment_items
I can use the presence of these relationships to determine what changes are allowed. There is no "inventory" per se, but invoicing is only allowed for items received (order_items with a related shipment_item - "you have it to sell.")
Dont know if it helps, but you can use a customized privilege set to prevent edition and deletion of a record when data is found on a related table.
This will only work for these privilege set and not if you access with a full access account. But since best practices are to not use full access account for endusing, i find this solution strong, clean and easy.
Tell me for more detail if interested.