Comment by pif
1 month ago
> I think we largely need support for "soft deletes" to be baked into SQL
I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page.
1 month ago
> I think we largely need support for "soft deletes" to be baked into SQL
I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page.
> I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page.
So a widespread, common and valid practice shouldn't be made better supported and instead should rely on awkward hacks like "deleted_at" where sooner or later people or ORMs will forget about those semantics and will select the wrong thing? I don't think I agree. I also don't think that it has much to do with how or where you represent the data. Temporal tables already do something similar, just with slightly different semantics.
What way of making it better supported wouldn’t require custom semantics that people would forget and then select the wrong thing.
> custom semantics
Making those custom semantics (enabled at per-schema/per-table level) take over what was already there previously: DELETE doing soft-deletes by default and SELECT only selecting the records that aren't soft deleted, for example.
Then making the unintended behavior (for 90% of normal operational cases) require special commands, be it a new keyword like DELETE HARD or SELECT ALL, or query hints (special comments like /*+DELETE_HARD*/).
Maybe some day I'll find a database that's simple and hackable enough to build it for my own amusement.