Posted by James Kaufman on 08/12/05 07:32

On Thu, Aug 11, 2005 at 10:47:09AM -0700, Saqib Ali wrote:
> Hello All,
>
> What are best practices for deleting records in a DB. We need the
> ability to restore the records.
>
> Two obvious choices are:
>
> 1) Flag them deleted or undeleted
> 2) Move the deleted records to seperate table for deleted records.
>
> We have a complex schema. However the the records that need to be
> deleted and restored reside in 2 different tables (Table1 and Table2).
>
> Table2 uses the primary key of the Table1 as the Foriegn key. The
> Primary key for Table1 is auto-generated. This make the restoring with
> the same primary key impossible, if we move deleted data to a
> different table. However if we just flag the record as deleted the
> restoring is quite easy.
>
> Any thoughts/ideas ?
>
> --
> In Peace,
> Saqib Ali
> http://www.xml-dev.com/blog/
> Consensus is good, but informed dictatorship is better.
>

I like the approach of marking them as deleted. It's easy to modify the SELECT
query to ignore deleted records, plus it's easy to restore them if you need
them.

--
Jim Kaufman
Linux Evangelist
public key 0x6D802619
CCNA, CISSP# 65668
---
Kato, what is going on in that little yellow brain of yours?
-- Chief Inspector Clouseau,
in reference to a priceless white Steinway piano.

[Back to original message]