3 ms·

I hate that I agree with the author overall. I mean I probably will keep adding a `deleted` column to my tables and cursing myself when I mess up queries to hid

I hate that I agree with the author overall. I mean I probably will keep adding a `deleted` column to my tables and cursing myself when I mess up queries to hide them but I can't help thinking they will save me a ton of headache one day... It's one of those "If you have it you will never need it but if you don't you will wish you had it"-types of things. I'm sure it's silly/stupid but it makes me feel better/safer.

eastbound 3y ago
Why not use a history table with JSON serialization of data, if the goal is to restore but not have to deal with orphan records and forgotten WHERE clauses?
graftak 3y ago
Would a safer method be to have a <entities>-deleted table and migrate a deleted record to that table when it’s ‘deleted’. I’m not versed in databases but I’d expect you can even set additional access rules to individual tables making cross referencing very hard.
- nielsbot 3y ago
  That's what the article suggests (at the end)
- joshstrange 3y ago
  Yes that would work and be safer in a number of ways. I think I always avoided that because it requires 2 operations (delete and insert) and a tiny bit of added complexity. I think I’ll try that approach the next time I do something like this.
- vaidhy 3y ago
  Yes and no. If you are thinking of a no-sql database, then this is an easy approach. However, I would expect most of the systems to be using normalized tables and this approach would be pretty hard due to relational constraints. You would have to have a separate database copy running for deleted entries so that it can be consistent within itself.
  - graftak 3y ago
    They way I see it relationships to the deleted record must always be nullified/marked deleted, because otherwise you get broken records referencing deletions. And relationships from a deleted record might as well persist because the foreign key will (fingers crossed) remain untouched. Again, not versed in db at all so this is likely total rubbish.
    - vaidhy 3y ago
      You are correct.. But if you move the deleted record to a different db, you have to follow the chain of relationships and move more records to the "deleted" db. Which means you would have a create a full database to maintain the entity relationships.
Sohcahtoa82 3y ago
Why not name your database "my_db_all" and then create a view called "my_db" that's just "select * from my_db_all where deleted != true", or something to that effect?
- waych 3y ago
  This seems to ignore the issues raised concerning foreign keys and consumer data protection pruning.
  - icedchai 3y ago
    Just because a row is soft-deleted now doesn't mean it can't be actually deleted later.
    - waych 3y ago
      This is still ignoring the issues listed in TFA?

eastbound 3y ago

Why not use a history table with JSON serialization of data, if the goal is to restore but not have to deal with orphan records and forgotten WHERE clauses?

graftak 3y ago

Would a safer method be to have a <entities>-deleted table and migrate a deleted record to that table when it’s ‘deleted’. I’m not versed in databases but I’d expect you can even set additional access rules to individual tables making cross referencing very hard.

nielsbot 3y ago
That's what the article suggests (at the end)
joshstrange 3y ago
Yes that would work and be safer in a number of ways. I think I always avoided that because it requires 2 operations (delete and insert) and a tiny bit of added complexity. I think I’ll try that approach the next time I do something like this.
vaidhy 3y ago
Yes and no. If you are thinking of a no-sql database, then this is an easy approach. However, I would expect most of the systems to be using normalized tables and this approach would be pretty hard due to relational constraints. You would have to have a separate database copy running for deleted entries so that it can be consistent within itself.
- graftak 3y ago
  They way I see it relationships to the deleted record must always be nullified/marked deleted, because otherwise you get broken records referencing deletions. And relationships from a deleted record might as well persist because the foreign key will (fingers crossed) remain untouched. Again, not versed in db at all so this is likely total rubbish.
  - vaidhy 3y ago
    You are correct.. But if you move the deleted record to a different db, you have to follow the chain of relationships and move more records to the "deleted" db. Which means you would have a create a full database to maintain the entity relationships.

Sohcahtoa82 3y ago

Why not name your database "my_db_all" and then create a view called "my_db" that's just "select * from my_db_all where deleted != true", or something to that effect?

waych 3y ago
This seems to ignore the issues raised concerning foreign keys and consumer data protection pruning.
- icedchai 3y ago
  Just because a row is soft-deleted now doesn't mean it can't be actually deleted later.
  - waych 3y ago
    This is still ignoring the issues listed in TFA?