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

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 .  
Adwait ChitaleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Adwait ChitaleyAuthor Commented:
thank you sir
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

I think  delete the data from table is right.
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.
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndCommented:
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.
RobinD, good additional useful points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.