4 ms·

This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the t

This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the table. But how can you ensure all other code in the organisation uses the view? Perhaps you add some access control to the table so that only authorized users can read directly from it, but that's even more technical overhead. Then you have foreign keys. If you have a "deleted" column in the Customer table you need to remake the Invoice table as a view so that it hides invoices for deleted customers. The same goes for the InvoiceItem table (foreign key of a foreign key) and all author auxiliary information related to the soft-deleted customers. Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! The author is right; soft-deletes are probably most definitely not worth it. There are many better ways to solve the problem.

munk-a 4y ago
I don't really agree with that. Within an organization you have documentation and instruction as tools - but you're also making the dumb approach (SELECT * FROM blah) the correct approach. If a user is writing a query against the DB, has no idea what the layout of the data is, and decides to prefer blahwithdeleted over blah then I'd really question whats going on at your organization - blahwithdeleted is pretty clearly self-documenting and it's likely a lot of your other domain specific tables with be much harder to naively discover your way through. I, personally, would in no way restrict access to blahwithdeleted, but I have made a pattern of it in our DB, there are about a dozen blahwithdeleted tables - each with a corresponding blah view... I usually get about one question per every two new employees about which table to use which I can answer in less than a minute with a helpful little explanation. I'd also mention I've not made a specific value statement on soft deletions in a general case since, if there was a clear general case solution we'd just all do that. This is a decision that needs to be made on a per table basis - it's a rather trivial decision in most cases, but it's very specific to the problem at hand.
croes 4y ago
If you mention the costs of reporting error you should consider the costs of wrongful hard delete too. And reports are developed and tested before they are used for crucial purposes.
tshaddox 4y ago
> Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!
- bjourne 4y ago
  > "the engineer queried the wrong table and thus got incorrect results." The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
  - tshaddox 4y ago
    In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted. Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
    - bjourne 4y ago
      "reasonably clear" are famous last words. It ignores tons of evidence on how commercial software development works. Everything is "reasonably clear" in isolation, but not when you throw in thousands of other "reasonably clear" things developers are supposed to keep track while not missing tight deadlines. Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is. Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS
bpicolo 4y ago
Give it the ol obvious name - table_including_soft_deleted_rows_dont_query_directly.
adrianmsmith 4y ago
> Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! This is not a great example of what you mean. If a customer purchased a product or subscription, paid for it, then later deleted their account, the company has received revenue so that data absolutely should be on revenue reports.

munk-a 4y ago

I don't really agree with that. Within an organization you have documentation and instruction as tools - but you're also making the dumb approach (SELECT * FROM blah) the correct approach. If a user is writing a query against the DB, has no idea what the layout of the data is, and decides to prefer blahwithdeleted over blah then I'd really question whats going on at your organization - blahwithdeleted is pretty clearly self-documenting and it's likely a lot of your other domain specific tables with be much harder to naively discover your way through. I, personally, would in no way restrict access to blahwithdeleted, but I have made a pattern of it in our DB, there are about a dozen blahwithdeleted tables - each with a corresponding blah view... I usually get about one question per every two new employees about which table to use which I can answer in less than a minute with a helpful little explanation. I'd also mention I've not made a specific value statement on soft deletions in a general case since, if there was a clear general case solution we'd just all do that. This is a decision that needs to be made on a per table basis - it's a rather trivial decision in most cases, but it's very specific to the problem at hand.

croes 4y ago

If you mention the costs of reporting error you should consider the costs of wrongful hard delete too. And reports are developed and tested before they are used for crucial purposes.

tshaddox 4y ago

> Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!

bjourne 4y ago
> "the engineer queried the wrong table and thus got incorrect results." The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
- tshaddox 4y ago
  In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted. Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
  - bjourne 4y ago
    "reasonably clear" are famous last words. It ignores tons of evidence on how commercial software development works. Everything is "reasonably clear" in isolation, but not when you throw in thousands of other "reasonably clear" things developers are supposed to keep track while not missing tight deadlines. Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is. Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS

bpicolo 4y ago

Give it the ol obvious name - table_including_soft_deleted_rows_dont_query_directly.

adrianmsmith 4y ago

> Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! This is not a great example of what you mean. If a customer purchased a product or subscription, paid for it, then later deleted their account, the company has received revenue so that data absolutely should be on revenue reports.