4 ms·
UPDATE orders SET is_deleted = true I mean, you didn't really delete the orders... does anyone DELETE things now a days?
by ErikAugust 3y ago
UPDATE orders
SET is_deleted = true
I mean, you didn't really delete the orders... does anyone DELETE things now a days?
- deleted 3y ago[deleted]
- OscarCunningham 3y agoI can't find the original post now, but there's a good suggestion that booleans should usually be timestamps: UPDATE orders SET deletion_timestamp = CURRENT_TIMESTAMP() WHERE deletion_timestamp IS NULL
- AntonZ234 3y agoInteresting, I’ve never heard about that pattern, only is_deleted everywhere
- kamikaz1k 3y agoThis makes more and more sense the more I think about it
- dingnuts 3y agoI can vouch for this pattern from personal experience. From an auditing perspective also, it can be really helpful to know WHEN something was removed.
- adhamsalama 3y agoThat's a good idea.
- FredPret 3y agoI love this pattern
- geekodour 3y agothis seems neat!
- postgressomethi 3y agoWhile that's kind of convenient in a lot of ways, it also makes querying the database really annoying, since you have to remember to add the filtering to every single query or you're screwed. Personally, I wish there was a database-implementation-acknowledged "deleted" flag, which could even expose a "history table"-like interface.
- Karellen 3y agoHow does using a timestamp column make querying the database more annoying than using a boolean column? Aren't the places you have to use filters exactly the same either way?
- postgressomethi 3y agoI'm sorry, I wasn't being clear. Any column meaning "this row is to be ignored for most applications" is an annoying pattern.
- postgressomethi 3y agoAh, now I see I responded to the wrong message.
- recursive 3y agoOnly for some tables. Depends on the usage.
- AntonZ234 3y agoRight, but as I had no way to understand which orders should be ‘hidden’ and which don’t, from the systems side it was the same as if I hard deleted everything.
- paulryanrogers 3y ago> does anyone DELETE things now a days? Yes. OLTPs work best when cold data is hard deleted from the hot instances. Backups and OLAP can keep things for longer, as needed. GDPR, right to be forgotten, and similar regulations may require deleting even scrubbed copies. (IANAL)
- FredPret 3y agoSome laws require a hard delete. But I agree, soft delete is much less risky
- dingnuts 3y agoEven in a system where you don't completely delete deleted records, you may move them to a new table, eg thing_deleted in certain situations (this is a real pattern I've encountered with justified reasons where merely setting delete_at would be insufficient for business logic reasons), which tends to be an INSERT and a DELETE in a transaction