Hi guys
Hi guys,
I'd like your opinion about something. Whenever I need to implement a "Delete" action I get very stressed.
Say for example, I want to implement Customers --> Quotes --> Invoices.
Apparently, there is functionality to add/edit Customer. Here I am reluctant to implement the delete function straight away because I am afraid that if I implement it before the Quotes/Invoices I may forget to delete them when a customer is deleted.
I know databases can take care of this with cascade/etc but many times things are more complex or databases do not have relationships
Do you ever get to this stage or it's just me? Should I just implement delete in the sequence of add/edit/delete?
Thanks
I'd like your opinion about something. Whenever I need to implement a "Delete" action I get very stressed.
Say for example, I want to implement Customers --> Quotes --> Invoices.
Apparently, there is functionality to add/edit Customer. Here I am reluctant to implement the delete function straight away because I am afraid that if I implement it before the Quotes/Invoices I may forget to delete them when a customer is deleted.
I know databases can take care of this with cascade/etc but many times things are more complex or databases do not have relationships
Do you ever get to this stage or it's just me? Should I just implement delete in the sequence of add/edit/delete?
Thanks
I generally have an update_date, delete_date, updated_by and deleted_by column in each base table and create an updatable view that the application uses. I use triggers to update the update_date and updated_by columns (it's easy to script generating the triggers) and "instead of" actions on the deletion of data from the views. The views filter out records with the delete date set. If the database does not support "instead of" actions, we just code a deletion as an update to the delete_date and deleted_by columns.
ReplyDeleteI also script the generation of cascade triggers to handle cascaded deletes and updates. There is also a script to delete underlying records with deleted dates set once a deleted record reaches a certain age.
Since all of this is at the database level and mostly is easily scripted, it allows the application to logically delete data but provides recoverability facilities to the database administrator. Over the years, it has proven helpful on occasion. For audit purposes, it has also helped identify when and by who data has been removed.
Performance-wise, so long as the number of cancelled records is not too great, it has not been a concern.
Rik van Kekem
ReplyDeleteI agree with your affirmation. Databases without relationships are, no more than a sack of data.
I have, what I think is a smart solution to this problem.
ReplyDeleteIn the case of the Invoices and his lines. I consider them a document. Because of this, you can delete an Invoice and the software shall know that you want the delete the whole related lines.
In the case of a Customer that has living Invoices.
You can't delete the Customer. Never! And the updates you make in his data shall not affect his previous Invoices.
How to delete old Customers, that are not currently used?
I have a table of Invoices with the prefix "HIST_" for History. This table has the definition as the original Customers, but... the reference constraints do not exists, all of them are denormalized. The same of course for his lines.
You shall implement a process called Pass Invoices to History, that move a row form Invoices to Hist_Invoices. In this same process, you can delete the moved invoice.
Now, you can delete the Customer if there are no references to the normal Invoices.
How to query for this old Invoices: Simply use UNION ALL SQL statement!
In the same window of the original Invoices, you have the option to show historic or not, because of the resulting columns of both parts of the query are equivalents: the first take referenced data and the second of the same table, but the column names are the same.
This method has the advantage of clean up the working table of invoices when the user think it's necessary, without lost data or information!