Re: Keeping deleted records
- From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
- To: wbfergus@xxxxxxxxx
- Date: Thu, 9 Aug 2007 12:04:29 -0500
One method is to leave your production data their and a column called
'deleted' or something along those lines, and then if the record is deleted,
just mark it as deleted but otherwise leave it their. Of course, the
retrievals have to change at that point to account for the logically deleted
data.
On 8/9/07, Bill Ferguson <wbfergus@xxxxxxxxx> wrote:
>
> Hi all,
>
> My management wants to keep a copy of all deleted records for historical
> purposes, in case somebody decides at a later point that the deletion was a
> mistake. This happens about once a year for probably a dozen or so records.
>
> The data structure is about 35 tables consisting of one master table with
> children attached to it. Only two of the child tables have their own
> children. This primarily a scientific record type database, of known
> worldwide mineral deposits, so the volume of transactions is fairly low,
> compared to what most of you are used to dealing with. I might have a couple
> hundred transactions per day on a busy day.
>
> I'm kind of torn between two approaches. The first approach is to
> basically create a duplicate schema that contains blank table structures,
> and as records are deleted, move them over to the "deletes" schema and then
> delete them from production. This will entail of bunch of redesign though on
> all of the triggers and others constraints in the new schema.
>
> The second approach would be to have the "deletes" schema be a copy of
> everything currently in the production schema, and then as new records are
> added, add them to the "deletes" schema, and if they're deleted, then I
> don't have to do anything. This approach though would entail the reworki9ng
> of all of the existing triggers in the production schema.
>
> Either way, management hasn't said what kind of tracking (if any) that
> they want for updates. It seems that they'd probably want that as well, so
> if a production record was updated, the new version of the record would be
> copied over to the "deletes" schema.
> Anybody else ever run across this kind of requirement before and have any
> ideas or suggestions on the best/easiest way to handle it?
>
> RMAN backups would really be a pain, as the deletes could have occured at
> any time and over a huge period of time as well.
>
> Thanks.
> --
> -- Bill Ferguson
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
