Re:Flickr Accidentally Deletes a User's 4,000 Photos and Can...
by derobert on Wednesday February 02, 2011 @01:19PM
(#2171342)
No, that's what views are for.
A sane approach would be to do all your table access via views, and the view definitions have the 'where is_deleted = 0' or whatever. Then your database handles it.
Updates to the view work as well, in pretty much every database system, including MySQL even.
Re:Flickr Accidentally Deletes a User's 4,000 Photos and Can...
by T-hawk on Thursday February 03, 2011 @12:33PM
(#2175626)
That just shifts the problem. Every piece of code and every developer now needs to know to hit that view rather than directly hit the table. Except the code that actually deals with deleting and undeleting which has to hit the table directly (or another view or sp.) And you still have the original problem in writing and managing the views themselves - every developer writing or maintaining one of those views needs to know to respect "where is_deleted = 0".
Using views adds a layer of abstraction. That's often a good idea and a step in the right direction, but does come at the cost of increasing the overall complexity of the system. It's an incremental step to the positive, but no revolutionary solution.
This comes with a cost, and it's not a small one. Every single query that ever touches this table, in every single piece of code owned by the company, written or maintained by every single developer (consultant or transient or offshore, none of which ever got training sessions on this requirement) needs to be enhanced to respect "where isDeleted <> true".
It's well understandable if the expected cost to recover after the fact from an occasional issue is smaller than the cost of implementing and maintaining that in the first place.