Delete is the most sensitive CRUD operation to perform on records in a table. The usual habit of composing and sending DELETE SQLs statements to the database processor is not very practical in most real-world applications. Database applications are to be designed to record and retain information in an organized manner and the deletion operation, even though necessary, should be monitored and controlled. This article lists the various methods I use to deal with the deletion operation in a database and how it is simulated to the end-user.
1. Controlled Deletion with Access Levels
Each user will belong to an access group and a few of these access groups are granted the right to perform deletions. Only members of these access groups can perform outright deletion of records. I usually keep the number of deletion-authorized access groups to one or two.
2. Recycle, Archive, Don’t Delete
This simply involves hiding records from view. The record table should have a status field and the application will display only records marked with a certain number for the status. The user on clicking the delete button changes this status number, therefore removing the record from view. Performing deletions this way will involve sending an appropriate UPDATE statement to change the `status` field. “Deleted” records can be accessed from a special view, much like Recycle Bin or Archive where records with the deletion status marking are displayed.
3. Delete, but Log Everything
This is the final step in controlling deletion where the actual dropping of the record is deemed necessary. The user is allowed to delete a record from a table, but after stating a reason for deletion which will be saved in a log table together with the user’s ID and timestamp. This way, even though the record is lost, the deleting user is held responsible and reasons for doing so is documented.
NOTE: In most real-world database application systems, that deal with a wide range of data types, a combination of all these methods and technoques could be employed to some extent.
0 likes