While working with a invoicing system back in 2013 I was asked by my customer if it would be possible to enable them to have some more special handling of their biggest client. The way their system was set up, one client accounted for over half of the accounts receivables. The client and my customer were also very close in their operations. What they needed from the system was the ability to split the client into four legal entities while remaining all the custom logic and prices. They asked me if a quick-fix was possible.
“A quick-fix solution is as fast, as easy and as well-documented as its explanation from the customer” – me just now.
The system had a procedure which would work out all the numbers for each client and for this particular one handle subsidiaries together on all invoices. With this reorganization coming, all units would be connected to one of four new client subsidiaries and each subsidiary would instead receive its own invoices for its units.
What the options for foreign key constraints do
You can add two clauses (ON UPDATE and ON DELETE) when creating a foreign key constraint. Both can have one of four options.
NO ACTION – this is standard, an error is raised and the execution halts if the new value would violate referential integrity.
CASCADE – data integrity is conserved by updating or deleting records in the related tables in the same transaction. In other words completely hazardous when dealing with deletes. Use with caution.
SET NULL – If the relation between records is broken, the integrity of the referencing record is upheld by setting the value of the foreign key column to NULL.
SET DEFAULT – Same as above except the value becomes the default for the column instead of NULL, there has to of course be a default value for the column for this to work.
From rough draft to the final solution
On the back of an envelope I wrote down these nine steps that would re-route the client logic and make sure everything would still sum up nicely:
- Begin a transaction and run the stored procedures to create the invoices as normal
- For each unit represented for the four new subsidiaries: create a new invoice header with a temporary and made up sequence starting from -1 and going backwards to avoid collisions.
- For each invoice line: map invoiceId by subsidiary (-1 through -4)
- Drop the now empty and unused invoice headers created in step 1.
- Drop the foreign key constraint between headers and lines
- Re-create this foreign key but set it with action ON UPDATE CASCADE
- Re-run the stored procedure that fixes invoiceIds for headers, this procedure is a sub-procedure when creating invoices. When the headers are updated, the lines inherit the information through our foreign key constraint.
- Drop and re-create the foreign key once again as it existed before the operation.
- Commit transaction. Done! No lines removed, all accounted for and all connected nicely.