3 ms·
> You never actually delete rows from your tables. You mark them as deleted, sure, but the data needs to be able to come back. Space is cheap, there are simply
by T-hawk 16y ago
> You never actually delete rows from your tables. You mark them as deleted, sure, but the data needs to be able to come back. Space is cheap, there are simply very few cases where outright deletion from a DB table is warranted.
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.
- derobert 16y agoNo, 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.
- T-hawk 16y agoThat 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.