Back to articles list
Often, it is the case that certain data should no longer be modified once it reaches a particular state. Good examples are completed orders and issued invoices. In some cases, it is even considered a crime to modify such data.
On one hand, we need to ensure immutable objects do not depend on operational data (for example, the current product catalog). On the other hand, they should be protected from accidental modification.
How to Ensure Objects Do Not Depend on Operational Data
Create completely separate tables. In our example, these are invoice and invoice_issued. When the invoice is issued, all data is copied from the operational table invoice to the invoice_issued table.
Of course, immutable tables can have references to operational tables. However, this should be done through additional linking tables to keep the data invariable (e.g., setting a reference to NULL is a modification and is not acceptable). In the example below, invoice and invoice_issued were linked by invoice_current_to_issued.
How to Protect Objects From Accidental Modification
One solution is to assign appropriate permissions to the tables. You may also create a trigger that throws an exception when trying to update and/or delete the record. Of course, both of these solutions can be implemented together.
