what's a standard process to manage the deletion records of table

Posted on 2009-05-22
Medium Priority
Last Modified: 2012-05-07
for handling deletion presently
we think to delete the data from table.
also we think for handling deletion we add one column status  in  all table.
when record is deleted then deleted record the status is set as deleted
please tell me which one is right .  
Question by:Adwait Chitaley
LVL 17

Accepted Solution

k_murli_krishna earned 2000 total points
ID: 24456966
Deleting data from table is hard/physical delete where as marking a IS_DELETED column to YES/NO is soft/logical delete. If you want to permanently delete records in a table or push/archive these records to a history/summary table then delete as it is or after migrating data to history/summary table. But if its okay to maintain history within same table itself since you will be searching for IS_DELETED as YES/NO then go for soft deletes. Which is better depends on the functionality and performance. DELETE takes place in background but SELECT is used to display data in front end. So, for SELECT performance when table is going to grow huge, a hard/physical delete with/without maintaining history is more desirable.

Author Comment

by:Adwait Chitaley
ID: 24457531
thank you sir
LVL 17

Expert Comment

ID: 24459037
Welcome, if you have any more doubts do ask them with more specific details from your side which helps me decide concretely, else close the question.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Expert Comment

ID: 24460306
I think  delete the data from table is right.
LVL 17

Expert Comment

ID: 24462033
If marking for soft delete thus retaining record in table itself is a functional requirement as per application, go for soft/logical deletion. In this also keep data type as smallint or even tinyint and use 0 and 1 values to decide soft deleted or not instead of charcter 'Y'/'N'. More specifically do not go for NULL value to decide soft deletion. But if you need to store the records some or the other place and need not join main table with summary/history/archive one then have a hard/physical deletion. But if you do not need the data at all simply delete hard permanently. Sometimes one may need to audit the CRUD i.e. INSERT/SELECT/UPDATE/DELETE operations, in which case you should transfer or store in a audit table as well. Soft/logical deletion is done normally from application where as migration/transfer to summary/history/archive/audit table is done by DBA using a trigger, a scheduled job or in a stored procedure/function.
LVL 17
ID: 24469240
If you are going for soft delete, it might be wise to add another two columns, one for which user did the delete and another for when it occured. You can undelete this record by another insert, but the old delete will still remain for tracking purposes.
Undeleting by just removing the soft 1/0 flag might cause problems in tracking as you will never be sure whether the record was in a delete state or not when whatever you are looking for occurred.
LVL 17

Expert Comment

ID: 24475488
RobinD, good additional useful points.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question