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?
How to manage deleted records, regarding history/reports
Moderator: General Moderators
How to manage deleted records, regarding history/reports
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
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.
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
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
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
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
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.
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
Re: How to manage deleted records, regarding history/reports
I wasn't saying you have to use doctrine, I'm just saying all of this can be done with doctrine
I think the real benefit comes when you need to stack many behaviors like this
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: - I can't define an unique constraint for a table, because the deleted items can be non unique (this is DB engine dependent);
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 triggers defined for "on delete" (in fact they become "on update when deleted=1") become much more complex;
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 recordsVladSun wrote: - the number of records to search from increases (this is DB engine dependent);
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 tooVladSun 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.
I think the real benefit comes when you need to stack many behaviors like this
Re: How to manage deleted records, regarding history/reports
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