Database design question. BIT column for deletions

Question

Hi guys,

Part of my table design is to include a IsDeleted BIT column that is set to 1 whenever a user deletes a record. Therefore all SELECTS are inevitable accompanied by a WHERE IsDeleted = 0 condition.

I read in a previous question (I cannot for the love of God re-find that post and reference it) that this might not be the best design and an 'Audit Trail' table might be better.

How are you guys dealing with this problem?

Update I'm on SQL Server. Solutions for other DB's are welcome albeit not as useful for me but maybe for other people.

Update2 Just to encapsulate what everyone said so far. There seems to be basically 3 ways to deal with this.

1. Leave it as it is
2. Create an audit table to keep track of all the changes
3. Use of views with WHERE IsDeleted = 0

Answer 1

We're actively using the "Deleted" column in our enterprise software. It is however a source of constant errors when forgetting to add "WHERE Deleted = 0" to an SQL query.

Not sure what is meant by "Audit Trail". You may wish to have a table to track all deleted records. Or there may be an option of moving the deleted content to paired tables (like Customer_Deleted) to remove the passive content from tables to minimize their size and optimize performance.

Answer 2

If you have Oracle DB, then you can use audit trail for auditing. Check the AUDIT VAULT tool form OTN, here. It even supports SQL Server.

Answer 3

A while ago there was some blog uproar on this issue, Ayende and Udi Dahan both posted on this.

Answer 4

If you have to have this kind of Deleted Bit column, then you really should consider setting up some VIEWs with the WHERE clause in it, and use those rather than the underlying tables. Much less error prone.

For example, if you have this view:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

Then someone who wants to see current products can simply write:

SELECT * FROM [Current Product List]

This is much less error prone than writing:

SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

As you say, people will forget that WHERE clause, and get confusing and incorrect results.

P.S. the example SQL comes from Microsoft's Northwind database. Normally I would recommend NOT using spaces in column and table names.

Answer 5

Nai this is totally up to you.

Do you need to be able to see who has deleted / modified / inserted what and when? If so, you should design the tables for this and adjust your procs to write these values when they are called.

If you dont need an audit trail, dont waste time with one. Just do as you are with IsDeleted.

Personally, I flag things right now, as an audit trail wasn't specified in my spec, but that said, I don't like to actually delete things. Hence, I chose to flag it. I'm not going to waste a clients time writing something they diddn't request. I wont mess about with other tables because that's another thing for me to think about. I'd just make sure my index's were up to the job.

Ask your manager or client. Plan out how long the audit trail would take so they can cost it and let them make the decision for you ;)

Answer 6

Udi Dahan said this:

Model the task, not the data Looking back at the story our friend from marketing told us, his intent is to discontinue the product – not to delete it in any technical sense of the word. As such, we probably should provide a more explicit representation of this task in the user interface than just selecting a row in some grid and clicking the ‘delete’ button (and “Are you sure?” isn’t it).

As we broaden our perspective to more parts of the system, we see this same pattern repeating:

Orders aren’t deleted – they’re cancelled. There may also be fees incurred if the order is canceled too late.

Employees aren’t deleted – they’re fired (or possibly retired). A compensation package often needs to be handled.

Jobs aren’t deleted – they’re filled (or their requisition is revoked).

In all cases, the thing we should focus on is the task the user wishes to perform, rather than on the technical action to be performed on one entity or another. In almost all cases, more than one entity needs to be considered.

Answer 7

Therefore all SELECTS are inevitable accompanied by a WHERE IsDeleted = 0 condition.

This is not a really good way to do it, as you probably noticed, it is quite error-prone. You could create a VIEW which is simply

CREATE VIEW myview AS SELECT * FROM yourtable WHERE NOT deleted;

Then you just use myview instead of mytable and you don't have to think about this damn column in SELECTs.

Or, you could move deleted records to a separate "archive" table, which, depending on the proportion of deleted versus active records, might make your "active" table a lot smaller, better cached in RAM, ie faster.

Answer 8

Views (or stored procs) to get at the underlying table data are the best way. However, if you have the problem with "too many cooks in the kitchen" like we do (too many people have rights to the data and may just use the table without knowing enough to use the view/proc) you should try using another table.

We have a complete mimic of the base table with a few extra columns for tracking. So Employee table has an EmployeeDeleted table with the same schema but extra columns for when it was deleted and who deleted it and sometimes even the reason for deletion. You can even get fancy and have triggers do the insertion directly instead of going through applications/procs.

Biggest Advantage: no flag to worry about during selects

Biggest Disadvantage: any schema changes to the base table also have to be made on the "deleted" table

Best for: situations where for whatever reason (usually political with us) many not-as-experienced people have rights to the data but still expect it to be accurate without having to understand flags or schemas, etc

Answer 9

I've used soft deletes before on a number of applications I've worked on, and overall it's worked out quite well. Yes, there is the issue of always having to remember to add AND IsActive = 1 to all of your SELECT queries, but really that's not so bad. You can create views if you don't want to have to remember to always do that.

The reason we've done this is because we had very specific business needs to be able to report on records that have been deleted. The reporting needs varied widely - sometimes they'd need to see just the active records, or just the inactive records, or sometimes a mix of both - so pushing all the deleted records into an audit table wasn't a very good option.

So, depending on your particular business needs, I think this approach is certainly a viable option.