In order to properly answer this question you first need to decide: What does "delete" mean in the context of this system/application?
To answer that question, you need to answer yet another question: Why are records being deleted?
There are a number of good reasons why a user might need to delete data. Usually I find that there is exactly one reason (per table) why a delete might be necessary. Some examples are:
- To reclaim disk space;
- Hard-deletion required as per retention/privacy policy;
- Corrupted/hopelessly incorrect data, easier to delete and regenerate
than to repair.
- The majority of rows are deleted, e.g. a log table limited to X records/days.
There are also some very poor reasons for hard-deletion (more on the reasons for these later):
- To correct a minor error. This usually underscores developer laziness and a hostile UI.
- To "void" a transaction (e.g. invoice that should never have been billed).
- Because you can.
Why, you ask, is it really such a big deal? What's wrong with good ole' DELETE?
- In any system even remotely tied to money, hard-deletion violates all sorts of accounting expectations, even if moved to an archive/tombstone table. The correct way to handle this is a retroactive event.
- Archive tables have a tendency to diverge from the live schema. If you forget about even one newly-added column or cascade, you've just lost that data permanently.
- Hard deletion can be a very expensive operation, especially with cascades. A lot of people don't realize that cascading more than one level (or in some cases any cascading, depending on DBMS) will result in record-level operations instead of set operations.
- Repeated, frequent hard deletion speeds up the process of index fragmentation.
So, soft delete is better, right? No, not really:
- Setting up cascades becomes extremely difficult. You almost always end up with what appear to the client as orphaned rows.
- You only get to track one deletion. What if the row is deleted and undeleted multiple times?
- Read performance suffers, although this can be mitigated somewhat with partitioning, views, and/or filtered indexes.
- As hinted at earlier, it may actually be illegal in some scenarios/jurisdictions.
The truth is that both of these approaches are wrong. Deleting is wrong. If you're actually asking this question then it means you're modelling the current state instead of the transactions. This is a bad, bad practice in database-land.
Udi Dahan wrote about this in Don't Delete - Just Don't. There is always some sort of task, transaction, activity, or (my preferred term) event which actually represents the "delete". It's OK if you subsequently want to denormalize into a "current state" table for performance, but do that after you've nailed down the transactional model, not before.
In this case you have "users". Users are essentially customers. Customers have a business relationship with you. That relationship does not simply vanish into thin air because they canceled their account. What's really happening is:
- Customer creates account
- Customer cancels account
- Customer renews account
- Customer cancels account
- ...
In every case, it's the same customer, and possibly the same account (i.e. each account renewal is a new service agreement). So why are you deleting rows? This is very easy to model:
+-----------+ +-------------+ +-----------------+
| Account | --->* | Agreement | --->* | AgreementStatus |
+-----------+ +-------------+ +----------------+
| Id | | Id | | AgreementId |
| Name | | AccountId | | EffectiveDate |
| Email | | ... | | StatusCode |
+-----------+ +-------------+ +-----------------+
That's it. That's all there is to it. You never need to delete anything. The above is a fairly common design that accommodates a good degree of flexibility but you can simplify it a little; you might decide that you don't need the "Agreement" level and just have "Account" go to an "AccountStatus" table.
If a frequent need in your application is to get a list of active agreements/accounts then it's a (slightly) tricky query, but that's what views are for:
CREATE VIEW ActiveAgreements AS
SELECT agg.Id, agg.AccountId, acc.Name, acc.Email, s.EffectiveDate, ...
FROM AgreementStatus s
INNER JOIN Agreement agg
ON agg.Id = s.AgreementId
INNER JOIN Account acc
ON acc.Id = agg.AccountId
WHERE s.StatusCode = 'ACTIVE'
AND NOT EXISTS
(
SELECT 1
FROM AgreementStatus so
WHERE so.AgreementId = s.AgreementId
AND so.EffectiveDate > s.EffectiveDate
)
And you're done. Now you have something with all of the benefits of soft-deletes but none of the drawbacks:
- Orphaned records are a non-issue because all records are visible at all times; you just select from a different view whenever necessary.
- "Deleting" is usually an incredibly cheap operation - just inserting one row into an event table.
- There is never any chance of losing any history, ever, no matter how badly you screw up.
- You can still hard-delete an account if you need to (e.g. for privacy reasons), and be comfortable with the knowledge that the deletion will happen cleanly and not interfere with any other part of the app/database.
The only issue left to tackle is the performance issue. In many cases it actually turns out to be a non-issue because of the clustered index on AgreementStatus (AgreementId, EffectiveDate) - there's very little I/O seeking going on there. But if it is ever an issue, there are ways to solve that, using triggers, indexed/materialized views, application-level events, etc.
Don't worry about performance too early though - it's more important to get the design right, and "right" in this case means using the database the way a database is meant to be used, as a transactional system.
