Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

The scenario is that I've got an expanding set of users, and as time goes by, users will cancel their accounts which we currently mark as 'deleted' (with a flag) in the same table.

If users with the same email address (that's how users log in) wish to create a new account, they can signup again, but a NEW account is created. (We have unique ids for every account, so email addresses can be duplicated amongst live and deleted ones).

What I've noticed is that all across our system, in the normal course of things we constantly query the users table checking the user is not deleted, whereas what I'm thinking is that we dont need to do that at all...! [Clarification1: by 'constantly querying', I meant that we have queries which are like: '... FROM users WHERE isdeleted="0" AND ...'. For example, we may need to fetch all users registered for all meetings on a particular date, so in THAT query, we also have FROM users WHERE isdeleted="0" - does this make my point clearer?]

(1) continue keeping deleted users in the 'main' users table
(2) keep deleted users in a separate table (mostly required for historical
    book-keeping)

What are the pros and cons of either approach?

share|improve this question
    
For what reasons do you keep the users? –  keppla Jul 20 '11 at 11:11
2  
This is called soft-delete. See also Deleting database records unpermenantley (soft-delete) –  Sjoerd Jul 20 '11 at 11:31
    
@keppla - he mentions that: "historical book-keeping". –  ChrisF Jul 20 '11 at 11:31
    
@ChrisF: i was interested in the scope: does he want to keep books of only the users, or is there still some data attached (e.G. comments, payments, etc) –  keppla Jul 20 '11 at 11:40
    
It might help to stop thinking of them as deleted (which is not true) and start thinking of their account as canceled (which is true). –  Mike Sherrill 'Cat Recall' Nov 29 '11 at 23:31

8 Answers 8

(1) continue keeping deleted users in the 'main' users table

  • Pros: simpler queries in all cases
  • Cons: may degrade performance over time, if there is a high number of users

(2) keep deleted users in a separate table (mostly required for historical book-keeping)

You may use e.g. a trigger to move deleted users to the history table automatically.

  • Pros: simpler maintenance for the active users table, stable performance
  • Cons: need different queries for the history table; however since most of the app is not supposed to be interested in that, this negative effect is probably limited
share|improve this answer
9  
A partition table (on IsDeleted) would remove the performance issues with using a single table. –  Ian Jul 20 '11 at 11:33
1  
@Ian unless every query is provided with IsDeleted as query criteria (which seems not in original question), partitioning may even cause performance degrade. –  Adrian Shum Jul 20 '11 at 11:45
1  
@Adrian, I was assuming that the most common queries would be at login time and that only none deleted users would be allowed to log in. –  Ian Jul 20 '11 at 11:47
1  
Use an indexed view on isdeleted if it becomes a performance issue and you want the benefit of a single table. –  JeffO Jul 20 '11 at 12:25

I strongly recommend using the same table. The main reason is data integrity. Most likely there are going to be many tables with relationships depending on users. When a user is deleted you do not want to leave those records orphaned.
Having orphaned records both makes enforcing constraints harder, and makes it more difficult to look up historic information. The other behavior to consider if when a user supplies a used email if you want them to recover all their old records. This would work automatically by using soft delete. As far as coding it, for instance in my current c# linq application the where deleted=0 clause is automatically appended to the end of all queries

share|improve this answer

"What I've noticed is that all across our system, in the normal course of things we constantly query the users table checking the user is not deleted"

This gives me a bad smell of design. You should hide such a kind of logic. For example, you should have a UserService providing a method isValidUser(userId) for use "across your system", instead of doing something like:

"get the user record, check if user is flagged as deleted".

Your way to store deleted user should not be affecting the business logic.

With such a kind of encapsulation, the above argument should no longer affect the approach of your persistence. Then you can focus more on the pros and cons related to persistence itself.

Things to consider include:

  • How long should the deleted record be actually purged?
  • What is the proportion of deleted records?
  • Will there be an issue for referential integrity (e.g. user is referred from other table) if you actually remove it from the table?
  • Are you consider re-opening the user?

Normally I would take a combined way:

  1. Flag the record as deleted (as to keep it for functional requirement, like reopening ac, or checking recently closed ac).
  2. After a predefined period, move the deleted record to archive table (for bookkeeping purpose).
  3. Purge it after some predefined archive period.
share|improve this answer
1  
[Clarification1: by 'constantly querying', I meant that we have queries which are like: '... FROM users WHERE isdeleted="0" AND ...'. For example, we may need to fetch all users registered for all meetings on a particular date, so in THAT query, we also have FROM users WHERE isdeleted="0" - does this make my point clearer?] @Adrian –  Alan Beats Jul 21 '11 at 1:10
    
Yup much clearer. :) If I am doing that, I would rather make it as user's status change, instead of looking it as physical/logical delete. Though the amount of code will not reduce ( "and isDeleted='0'" vs ' and "state <> 'TERMINATED' ") but everything will look much more reasonable, and it is normal to have different user state too. Periodic-purge of TERMINATED users can be performed too, as suggested in my previous answer) –  Adrian Shum Jul 21 '11 at 3:56

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.

share|improve this answer

I am currently working with a system presently where every table has a Deleted flag for soft-delete. It is the bane of all existence. It totally breaks relational integrity when a user can "delete" a record from one table, yet children records which FK back to that table are not cascade soft-deleted. Really makes for trash data after time passes.

So, I recommend separate history tables.

share|improve this answer
    
Surely without cascaded history-shifts, you have exactly the same problem? –  glenatron Jul 20 '11 at 14:18
    
Not in your active record tables, no. –  Jesse C. Slicer Jul 20 '11 at 15:12
    
So what happens to child records who FK off the user table after the user has been consigned to the history table? –  glenatron Jul 20 '11 at 16:15
    
Your trigger (or business logic) would consign the child records to their respective history tables as well. The point is, you can't physically delete the parent record (for moving to history) without the database telling you that you broke RI. So you're forced to design it in. Deleted flag does not force cascading soft-deletes. –  Jesse C. Slicer Jul 20 '11 at 16:20
2  
Depends what your soft delete really means. If it's just a way to deactivate them, there's no need to adjust records related to a deactivated account. Seems like just data to me. And yes, I have to deal with it as well in a system I didn't design. Doesn't mean you have to like it. –  JeffO Jul 20 '11 at 20:58

To break the table in two would be the lamest thing imaginable.

Here are the two very simple steps that I would recommend:

  1. Rename the 'users' table to 'allusers'.
  2. Create a view called 'users' as 'select * from allusers where deleted=false'.

P.S. Sorry for the several-month-long delay in answering!

share|improve this answer

If you'd been recovering deleted accounts when someone comes back with the same e-mail address then I would have gone with keeping all the users in the same table. This would make the account recovery process trivial.

However, as you create new accounts then it would probably be simpler to move deleted accounts to a separate table. The live system doesn't need this information so don't expose it. As you say it makes the queries simpler and quite possibly quicker on larger datasets. Simpler code is also easier to maintain.

share|improve this answer

You do not mention DBMS in use. If you have Oracle with proper license, you can consider partitioning the users table into two partitions:active and deleted users.

share|improve this answer
    
Then you must move rows from one partition to another when deleting users, which is definitely not how partitions are intended to be used. –  Péter Török Jul 20 '11 at 12:42
    
@Péter: Huh? You can partition on any criteria you want, including the deleted flag. –  Aaronaught Jul 21 '11 at 2:44
    
@Aaronaught, OK, I phrased it wrong. The DBMS can do the work for you, but it is still extra work (because the row must be physically moved from one location to another, possibly to a different file), and it may deteriorate the physical distribution of data. –  Péter Török Jul 21 '11 at 9:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.