Post reply

Deleting records but maitaining history

  • 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.

  • March 1, 2010 at 7:21 am

    #1126448

    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.

  • March 1, 2010 at 7:34 am

    #1126451

    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?

  • March 1, 2010 at 2:00 pm

    #1126695

    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/

  • March 1, 2010 at 3:33 pm

    #1126756

    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]

  • March 2, 2010 at 1:47 am

    #1126893

    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.

  • March 2, 2010 at 9:59 am

    #1127235

    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.

  • March 3, 2010 at 11:15 am

    #1127965

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

    Thanks again.

  • March 9, 2010 at 9:43 am

    #1130750

    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.

  • March 10, 2010 at 12:46 pm

    #1131624

    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

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

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