Status: Open (View Workflow)
How do we maintain reference integrity when deleting records that are referenced elsewhere? While technically this problem exists both within the context of a single module and across more than one module, the latter case is more challenging and the focus of this story.
- Orders (piece) references an inventory (item) record. If the item is removed, the reference in the piece will point to a record that no longer exists.
- Various records in orders, organizations, invoice, finance reference acquisitions-unit records. If an acquisition-unit is removed, this could be problematic for those records that are associated with it.
- Circulation (loan) references an inventory (item) record. If the item is removed, the reference in the loan will point to a record that no longer exists.
The tech leads group discussed this topic briefly. Here are some of the solutions discussed:
- soft delete - don't delete the record, but mark it as "deleted" and adjust queries and/or business logic so that these records are hidden.
- prevent deletion of referenced record - before deleting a record, search for records that reference it, and if any are found, prevent the record from being deleted
- cascading delete - may make sense in some cases if everything is within a single module, but how do you know for sure that it isn't referenced elsewhere?
- pub-sub: notification - publish an event whenever a record is deleted, allowing other modules to clean up references, or perform other appropriate actions.
- pub-sub: ask for permission to delete - I want to delete a record, ask other modules if it's ok to proceed