Database design question. BIT column for deletions

Question

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

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.