From a chat with Buddy.
(2:19:29 PM) Me: sometimes i wish sql, or at least individual RDBMSs, had a built-in way to "disable" a row, simply cause i run into this pattern so much :)
(2:19:46 PM) Buddy: disable a row?
(2:25:04 PM) Me: yeah, here's a sample use case, not necessarily fully thought out :)... I have to add (well I'm debating whether it's worth the effort) the ability to delete a product... but burndown_metric references product, and transaction references burndown_metric...I absolutely can't delete rows in transaction. So I have to add an enabled flag to product, and that flag will have to be checked in almost all queries (either that or i'll make something like product_v that filters out enabled=false)...so what if I could avoid all this and, instead of "DELETE FROM product WHERE ..." I could do "DISABLE FROM product WHERE ..." - which makes it so for all queries, by default, the row is considered deleted and gone just about automatically, but foreign keys aren't broken...and for those occasions where you want to see the disabled data, maybe you could do something special like "SELECT FROM product WHERE ... INCLUDE DISABLED"
(2:31:35 PM) Buddy: ah word, just aboput every single one of my tables I have a deleted flag (I store a datetime rather then boolean, the time it was deleted) so many of my queries have "where deleted is null"
(2:32:09 PM) Me: yeah
(2:32:29 PM) Me: just such a common pattern it seems, and i hate having to go through this time and time again :)
(2:37:08 PM) Me: in reality even if a database added the feature today, most people would be ignorant of it, and when it's used it would baffle everyone...getting results they didn't expect and not knowing about INCLUDE DISABLED
(2:38:44 PM) Me: Table A has a foreign key to Table B...yet you join the two and get no results