Virtual Delete Solutions

[hrishy]

Hi Amar

virtual delete is something like this..when a employee leaves the organisation the employee record is deleted by the Human resources team from the front end application by clicking on the delete button (virtual delete) but in the backend a trigger executes and that record is moved into a history table..or a delete column is filled with Y

chris

I have used things like using a trigger to move that record into a history table.for example when emp record is deleted a employee ecord is moved to employee_history table and the child table containing dependents is also moved to dependents_history table.

You can also use a column or a flag as you would like to call it. marked it as deleted.

If you have a global HR application that is implemented 1st method is preferrable..when you have millions of records..(and a high attrition rate ;-)


I am curious to know how did you think of a generic table and a xml format..i would not prefer XML unless you want to do some EDI or B2B stuff..my experience with XML is it is slow because of the parsing overhead..

Yes in the above scenarios

I would add indexes
I would generate histograms on indexed columns

Undeletes in this case should be treated as a separate insert.Additonaly there would be a timestamp column in the employee history table which would capture the time the record was deleted ,who deleted etc.(when employee left the organisation)if he rejoins back at a later time then it would be a brand new insert.



what are your requirements like

regards
Hrishy