Database design issue. BIT column to delete

Question

Part of my table project should include the IsDeleted BIT column, which is set to 1 when the user deletes the record. Therefore, all CHOICES are inevitable, followed by the WHERE IsDeleted = 0 condition.

I read in the previous question (I cannot, that the love of God found this position again and referred to it) that this may not be the best design, and the Audit Trail table may be better.

How do you guys solve this problem?

Update I'm on SQL Server. Solutions for other databases are welcome, although not so useful for me, but, possibly, for other people.

Update2 Just to encapsulate what everyone has said so far. There seem to be basically 3 ways to handle this.

• Leave it as is
• Create an audit table to track all changes.
• Using Views with WHERE IsDeleted = 0

Answer 1

If you should have this “Remote bit” column, you really should consider setting up some VIEWs with a WHERE clause in it and use them instead of the base 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 this WHERE clause and get confused and incorrect results.

P.S. SQL Microsoft Northwind. .

Answer 2

, WHERE IsDeleted = 0.

, , , , . VIEW,

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

myview mytable, SELECT.

"", "" , , .. .

Answer 3

"" . , "WHERE Deleted = 0" SQL-.

, "Audit Trail". , . (, Customer_Deleted) , .

Answer 4

, Ayende Udi Dahan, .

Answer 5

Oracle DB, . AUDIT VAULT OTN, . SQL Server.

Answer 6

, .

, // ? , procs .

, . , IsDeleted.

, , , . . , , . , , . , .

. , , ;)

Answer 7

:

, , , - - - . , , , , "" ( " ?", ).

, , :

arent - theyre . , .

arent - theyre (, , ). .

- ( ).

, , , . .

Answer 8

( ) . , " ", ( , , /proc ), .

. , Employee EmployeeDeleted , , , , . , , /procs.

: ,

: ""

: , - ( ) - , , , ..

Answer 9

, , . , , AND IsActive = 1 SELECT, . , , .

, , , -, . - , , - .

, , , , , .