I am wondering what DBA would recommend when I would like to delete a record in table. Do you delete the record permanently or store it in such a way that from user perspective it is deleted but it is still there in database actually? Means for track history, should one keep the old records or just delete them permanently. I am not quite sure what is the general trend out in the market! Say for example, if a user posts a wrong post in a forum. As an admin, I would delete this post but for future reference, I would want to know that 'x' user had posted so-and-so post which was deleted! Or should I never bother with this and just delete it right away!
I never delete records from my tables. I am using a field like isDeleted and I am setting it to 1. And all queries are selecting those tables with isDeleted=0. If you have relational tables, in some cases If you delete a record, other tables can affect from this. For example: If we have a company_users table, and a company_users_action table. You may want to delete a user but you may not want to delete user's actions for future reference. If you delete users you may not know that actions was whose actions.
I agree. Most dba's use an IsDeleted or IsActive column and only do logical deletes. It all depends on the application of course.
i do the same thing however, wanted an opinion from Guru's in general, what is their recommendation!!
well I work as a DBA and we even had some consultants from MS come over and that also was their recommendation.. always logical deletes, physical deletes are an exception
This also depends on the size of databases you are using. If it is a very large database then you definitelly do not want to store records that will never be used again and you can delete them while you make sure that you also delete the associated records Howeber a better approach would be to store removed records in separate tables if you feel you will ever need them again
I sometimes do something like the Windows "recycle bin", I have a table with fields that identify the deleted data along with date etc. with the data in a compressed format.