Page 1 of 1

How to manage deleted records, regarding history/reports

Posted: Sat Oct 04, 2008 11:03 am
by VladSun
I define and use a "deleted" field in every DB table, which is used in my reports. When the admin deletes an item, this flag is set to true and no data is really deleted.
This way I'm able to create "data-lossless" reports by discarding any "delete" actions done by the admins.

What is bothering me is that this way I loose some existing functionality:
- I can't define an unique constraint for a table, because the deleted items can be non unique (this is DB engine dependent);
- the triggers defined for "on delete" (in fact they become "on update when deleted=1") become much more complex;
- the number of records to search from increases (this is DB engine dependent);

I'm thinking of another approach - by having a "deleted items" table. Of course, this leads to more complex queries. Also, their execution is slower because I must use separate indexes for each table.

What would be best to use? Any other ideas?

Re: How to manage deleted records, regarding history/reports

Posted: Sat Oct 04, 2008 1:48 pm
by alex.barylski
I would just mirror the tables you wish to backup.

What extra querying is there, really?

When you delete a record, copy it's contents first, create a new backup record with the same PKID. Then delete the original.

You only need to query the mirrored tables when you want to revert some action. You can drop all the conditional tests for "deleted" flags thus simplifiying your queries and probably speeding them up significantly over time.

Re: How to manage deleted records, regarding history/reports

Posted: Sat Oct 04, 2008 1:57 pm
by VladSun
It's not about backup ... Lets say the main target for doing this are the time based reports. It doesn't matter whether an item has been deleted if it existed at some point in the time interval of the report. It did exist then, so it must be included in the report.

Re: How to manage deleted records, regarding history/reports

Posted: Sat Oct 04, 2008 2:02 pm
by alex.barylski
I see. So couldn't you still use two separate tables and just group the entries by timestamp or something?

Re: How to manage deleted records, regarding history/reports

Posted: Sat Oct 04, 2008 6:52 pm
by josh
I'd recommend Doctrine behavioral templating, lets you just call ->delete() and ->select() and code your "soft delete functionality" as a behavior that runs under the hood. It has the ability to do field validation before the data gets sent to the database, like you could implement custom validations for your unique records

Re: How to manage deleted records, regarding history/reports

Posted: Sun Oct 05, 2008 8:30 am
by VladSun
As far as I can understand, doctrine is just another DB layer. I don't think it will help me a lot.
I've worked with .NET datasets (which are very similar to "doctrine" IMHO) and the problems involved by using the deleted flag are almost doubled. That's why I don't think doctrine is really the solution for my problem.

Re: How to manage deleted records, regarding history/reports

Posted: Sun Oct 05, 2008 3:31 pm
by josh
I wasn't saying you have to use doctrine, I'm just saying all of this can be done with doctrine
VladSun wrote: - I can't define an unique constraint for a table, because the deleted items can be non unique (this is DB engine dependent);
You can define custom behaviors that hook into CRUD actions, and execute arbitrary code, you can write your own custom validaters, to enforce integrity on the code level not just the database layer ( which is recommended anyways )
VladSun wrote: - the triggers defined for "on delete" (in fact they become "on update when deleted=1") become much more complex;
from my understanding youd just call ->delete() and ->select() and the abstraction layer would take care of worrying about the deleted field, you might have to extend the table gateway and write your own finder method to list recycling bin contents.
VladSun wrote: - the number of records to search from increases (this is DB engine dependent);
well since you can write practically anything into your ORM layer, you could automatically partition data into mutliple databases, not sure how using an abstraction layer is going to make you search more records
VladSun wrote: I'm thinking of another approach - by having a "deleted items" table. Of course, this leads to more complex queries. Also, their execution is slower because I must use separate indexes for each table.
you could easily write a few lines of code in doctrine to give each table a copy with a _deleted suffix, and just move records between the two tables, when you export your models doctrine ( or whatever ORM ) would create the structures, then each table has it's own index too

I think the real benefit comes when you need to stack many behaviors like this

Re: How to manage deleted records, regarding history/reports

Posted: Mon Oct 06, 2008 4:18 am
by VladSun
OK, thanks. I'll take a deeper look at it :)