How to manage deleted records, regarding history/reports

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

How to manage deleted records, regarding history/reports

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

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

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

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

Post by alex.barylski »

I see. So couldn't you still use two separate tables and just group the entries by timestamp or something?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

OK, thanks. I'll take a deeper look at it :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply