Deleting records but maitaining history

Question

Post reply

Deleting records but maitaining history

dias.nanda

SSC Veteran

Points: 245
More actions
March 1, 2010 at 7:08 am

#217129

Hi,
I’m remodeling the project’s database and I need to provide the delete functionality for some entities.
The issue here is that I need to keep the deleted records to provide several reports.
Which is the best approach? Add a flag column on the entities I need to delete and consider this flags on the queries or maintaining these “dead” data on another database to be used for the reports?
Any other suggestion?
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

Why do you need to delete records?

If you're considering flagging them as deleted, why do you need to delete at all?

Converting oxygen into carbon dioxide, since 1955.

dias.nanda SSC Veteran Points: 245 More actions · Answer 2

I need to delete for UI proposes. The user must be able to delete some records. But I need the data so I can use it to generate reports, because the reports need to consider the deleted data.

That is, if the user deletes a record of entity X, he cannot see that record on the UI again, but I need that record to generate some reports...

Any ideas?

Ray K SSC-Dedicated Points: 31165 More actions · Answer 3

dias.nanda (3/1/2010)
I need to delete for UI proposes. The user must be able to delete some records. But I need the data so I can use it to generate reports, because the reports need to consider the deleted data.
That is, if the user deletes a record of entity X, he cannot see that record on the UI again, but I need that record to generate some reports...
Any ideas?

Yes, one idea . . .

I'd consider creating a table nearly identical in structure to the one from which you want to delete, and call it a "history" or "archive" table. Before a record is actually deleted, copy it (in T-SQL parlance, insert it) to the new table. Once the record is in the history table, perform the delete. (Also, for the new table, you might want to consider an additional datetime field that records when the record was inserted/deleted.)

Hope that helps!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

I need to delete for UI proposes

Question: Does the UI use dynamic SQL or call a stored procedure?

Question. How long does the "deleted" row need to be available for reporting purposes?

Question: Is the "deleted" row somehow designated as such in the report?

Question: What SQL server (2000, 2005, 2008) are you using?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Andrei Hetel Old Hand Points: 393 More actions · Answer 5

I wouldn't use a flag in the active table, queries based on active = 0/1 condition will most likely slow down your application.

My idea would be to create a history table, as suggested above and copy deleted data to it based on a delete trigger.

aureolin Hall of Fame Points: 3520 More actions · Answer 6

Including the date and time of when the record was 'deleted' is very useful. I'd also add a field to allow you to track the user that did the deletion.

Steve G.

dias.nanda SSC Veteran Points: 245 More actions · Answer 7

Thanks for the ideas guys! I guess I'll be using archive tables and triggers to perform the "sync".

Thanks again.

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 8

Andrei Hetel (3/2/2010)
queries based on active = 0/1 condition will most likely slow down your application

I very much doubt there will be any performance issue from using a flag in the WHERE clause.

If the records are still required for reporting purposes, then they are not really "archive". You may find that having secondary tables creates a design overhead when modifications are required.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

we have archive databases where we put the data before deleting it

First archive the data

attempt delete

delete data in other tables if there are FK issues

delete data

on some tables we also make copies once or twice a month and store them in archive databases