6 ms·

>Soft deletes This section is totally wrong IMO. What is the alternative? "Hard" deleting records from a table is usually a bad idea (unless it is for legal re

by rm999 5y ago

>Soft deletes This section is totally wrong IMO. What is the alternative? "Hard" deleting records from a table is usually a bad idea (unless it is for legal reasons), especially if that table's primary key is a foreign key in another table - imagine deleting a user and then having no idea who made an order. Setting a deleted/inactive flag is by far the least of two evils. >when multiplied across all the analytics queries that you’ll run, this exclusion quickly starts to become a serious drag I disagree, modern analytics databases filter cheaply and easily. I have scaled data orgs 10-50x and never seen this become an issue. And if this is really an issue, you can remove these records in a transform layer before it hits your analytics team, e.g. in your data warehouse. >soft deletes introduce yet another place where different users can make different assumptions Again, you can transform these records out.

tgbugs 5y ago
Hard deletes are also awful from the perspective of data preservation. For example, when youtube removes a video they also delete all the metadata or any indication that it ever existed. Countless people have lost what they thought was a secure record of at least the title of songs or videos they saved to a playlist. There is also a more sinister side, which is that the ability to hard delete something forever means that bad actors can fabricate old "deleted" documents and accuse someone of having created and then deleted them.
- bulhi 5y ago
  Exactly. I get OP's point (i.e. you can accidentally include softdeleted records in your results), but for some types of data hard deletes are an absolute no-go anyways, so you just have to live with it.
- alexpetralia 5y ago
  I do think that hard deletes may sometimes be required to comply with legal requirements (e.g. complete expungement of personal information relating to a user). If it is not required by statutory law, sometimes it is written into commercial contracts.
irrational 5y ago
> if that table's primary key is a foreign key in another table - imagine deleting a user and then having no idea who made an order Assuming you have constraints set up correctly (on delete no action or on delete restrict) then how could this ever happen? If you don’t have constraints set up correctly…
- andrewprock 5y ago
  The chance that you don't have constraints set up correctly is indistinguishable from 100%.
  - irrational 5y ago
    I disagree. Any fairly competent DBA will know how to setup the constraints correctly. It's not rocket science. If you can think logically enough to program, you can think logically enough to set up constraints correctly.
    - cerved 5y ago
      Not everybody has a DBA :(
      - irrational 5y ago
        Well, my team has never had an official DBA either. But we do all the tasks of a DBA. I've installed/upgraded, configured, tested backups, and hardened Oracle and Postgres more times than I can remember. We do all our own DML and DDL work. It really isn't hard to do it right.
    - ako 5y ago
      This should be the responsibility of the application developer creating the database schema and queries. A constraint is part of your application logic, not of the administration of the database.
    - yxhuvud 5y ago
      I've yet to see anything I'd consider calling a startup having a DBA. I'm positively impressed if they even default to use foreign keys.
watermelon0 5y ago
Hard deletes most likely need to be supported, due to legal or contractual obligations. Designing with this in mind, makes everything a lot easier in the long run.
- rm999 5y ago
  I’ve always NULL’d values, not deleted rows. E.g. GDPR request? NULL out all identifying information, but keep the record. As long as your primary key has no business meaning you should never have to delete the row of a table.
  - FigmentEngine 5y ago
    INAL, but... you might want to revisit that code. article 17, right to erasure is about erasure of personal data, not about making non-indentifiable. of course they dont define erase or delete :-) (edit: typo)
    - Aperocky 5y ago
      well to me the transaction is the same as deleting a record and populating a NULL record. I don't see why the law should care in any way about a company populating NULL records.
      - FigmentEngine 5y ago
        "NULL out all identifying information" is anonymization, not deleting the information.
        duncans 5y ago
        I wrote zeroes to my hard drive. Would you consider the data on my hard drive merely anonymized, or is it deleted?
        ranguna 5y ago
feoren 5y ago
Secret hacker pro-tip: don't use foreign key constraints. At all. They're incompatible with the goals of most modern software systems. Ssh. It's a secret. In before downvotes because your textbook/groupthink told you otherwise.
inopinatus 5y ago
Most order forms are snapshots of data at the instant of their lodgement, since they are sales contracts. It is a rookie blunder to link them relationally to master data for products and PII &c. The record of an order is not intrinsically PII and thereby subject to rights of erasure. It may well be equally unlawful in some jurisdictions to irrevocably destroy it entire, it being necessary for accounting or tax audit, or even simply for mundane followup process, such as returns, that arise from actionable consumer rights. Ergo, such documents must fundamentally survive the erasure/redaction of any PII it does include.
- jhgb 5y ago
  > It is a rookie blunder to link them relationally to master data for products and PII &c. Is it always? If that data is immutable, for example?
  - snowwrestler 5y ago
    How are you going to satisfy data compliance, which may require the deletion of PII upon request or expiration, if your PII data is immutable?
    - atatatat 5y ago
      Was just considering this when I came across your comment. I'm hoping someone here can suggest a one-way audit-log audit-trail sort of solution, because I need this for the medical industry.
      - deleted 5y ago
        [deleted]
      - ranguna 5y ago
        I would say the structure of the records themselves can stay, but not the data itself. If you have a user table, maybe you can just randomly hash the sensitive user data but keep the record. Not 100% sure about this though, since you could probably derive the user with statistics like: if it's known that 1 person gets a specific disease every 10 years and you have an obfuscated record of a person connected with that disease, it's fairly straightforward to derive who that person is just through that connection.

by rm999 5y ago

tgbugs 5y ago

Hard deletes are also awful from the perspective of data preservation. For example, when youtube removes a video they also delete all the metadata or any indication that it ever existed. Countless people have lost what they thought was a secure record of at least the title of songs or videos they saved to a playlist. There is also a more sinister side, which is that the ability to hard delete something forever means that bad actors can fabricate old "deleted" documents and accuse someone of having created and then deleted them.

bulhi 5y ago
Exactly. I get OP's point (i.e. you can accidentally include softdeleted records in your results), but for some types of data hard deletes are an absolute no-go anyways, so you just have to live with it.
alexpetralia 5y ago
I do think that hard deletes may sometimes be required to comply with legal requirements (e.g. complete expungement of personal information relating to a user). If it is not required by statutory law, sometimes it is written into commercial contracts.

irrational 5y ago

> if that table's primary key is a foreign key in another table - imagine deleting a user and then having no idea who made an order Assuming you have constraints set up correctly (on delete no action or on delete restrict) then how could this ever happen? If you don’t have constraints set up correctly…

andrewprock 5y ago
The chance that you don't have constraints set up correctly is indistinguishable from 100%.
- irrational 5y ago
  I disagree. Any fairly competent DBA will know how to setup the constraints correctly. It's not rocket science. If you can think logically enough to program, you can think logically enough to set up constraints correctly.
  - cerved 5y ago
    Not everybody has a DBA :(
    - irrational 5y ago
      Well, my team has never had an official DBA either. But we do all the tasks of a DBA. I've installed/upgraded, configured, tested backups, and hardened Oracle and Postgres more times than I can remember. We do all our own DML and DDL work. It really isn't hard to do it right.
  - ako 5y ago
    This should be the responsibility of the application developer creating the database schema and queries. A constraint is part of your application logic, not of the administration of the database.
  - yxhuvud 5y ago
    I've yet to see anything I'd consider calling a startup having a DBA. I'm positively impressed if they even default to use foreign keys.

watermelon0 5y ago

Hard deletes most likely need to be supported, due to legal or contractual obligations. Designing with this in mind, makes everything a lot easier in the long run.

rm999 5y ago
I’ve always NULL’d values, not deleted rows. E.g. GDPR request? NULL out all identifying information, but keep the record. As long as your primary key has no business meaning you should never have to delete the row of a table.
- FigmentEngine 5y ago
  INAL, but... you might want to revisit that code. article 17, right to erasure is about erasure of personal data, not about making non-indentifiable. of course they dont define erase or delete :-) (edit: typo)
  - Aperocky 5y ago
    well to me the transaction is the same as deleting a record and populating a NULL record. I don't see why the law should care in any way about a company populating NULL records.
    - FigmentEngine 5y ago
      "NULL out all identifying information" is anonymization, not deleting the information.
      - duncans 5y ago
        I wrote zeroes to my hard drive. Would you consider the data on my hard drive merely anonymized, or is it deleted?
        ranguna 5y ago

feoren 5y ago

Secret hacker pro-tip: don't use foreign key constraints. At all. They're incompatible with the goals of most modern software systems. Ssh. It's a secret. In before downvotes because your textbook/groupthink told you otherwise.

inopinatus 5y ago

Most order forms are snapshots of data at the instant of their lodgement, since they are sales contracts. It is a rookie blunder to link them relationally to master data for products and PII &c. The record of an order is not intrinsically PII and thereby subject to rights of erasure. It may well be equally unlawful in some jurisdictions to irrevocably destroy it entire, it being necessary for accounting or tax audit, or even simply for mundane followup process, such as returns, that arise from actionable consumer rights. Ergo, such documents must fundamentally survive the erasure/redaction of any PII it does include.

jhgb 5y ago
> It is a rookie blunder to link them relationally to master data for products and PII &c. Is it always? If that data is immutable, for example?
- snowwrestler 5y ago
  How are you going to satisfy data compliance, which may require the deletion of PII upon request or expiration, if your PII data is immutable?
  - atatatat 5y ago
    Was just considering this when I came across your comment. I'm hoping someone here can suggest a one-way audit-log audit-trail sort of solution, because I need this for the medical industry.
    - deleted 5y ago
      [deleted]
    - ranguna 5y ago
      I would say the structure of the records themselves can stay, but not the data itself. If you have a user table, maybe you can just randomly hash the sensitive user data but keep the record. Not 100% sure about this though, since you could probably derive the user with statistics like: if it's known that 1 person gets a specific disease every 10 years and you have an obfuscated record of a person connected with that disease, it's fairly straightforward to derive who that person is just through that connection.