How implement a cancel attribute in a table?

Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled.

For canceled data I want to store the additional information

• by whom

• when

• why

In about 95 % of the queries I only want to select the non canceled rows.

Further I want to be able implement unique constraints to some columns for the not canceled columns.

What are the pros and cons of using NULL in some column as indicator for not canceled ?

I'm not sure I like NULL for lack of being cancelled. I think I'd rather have an Active flag that is 1 by default, and set to 0 when something is cancelled. Now your checks are simply WHERE active = 1 or WHERE active = 0, instead of dealing with all the OR IS NULL or OR IS NOT NULL checks, and a row can be inactive for other reasons other than "canceled" should that model ever mature into other reasons. You can use filtered indexes to make the queries of one type or the other slightly more efficient if you are using SQL Server 2008+. NULL implies unknown, and in the case where you only have two states, "not cancelled" is not unknown.

As for the who, why, when? Perhaps that is a separate table altogether, and can act like an audit log (including if entities are marked as cancelled, then made active again, then cancelled again, etc). It would just be something like:

CREATE TABLE dbo.AuditLog
(
    key INT NOT NULL FOREIGN KEY REFERENCES dbo.(key),
    action NVARCHAR(32) NOT NULL, 
      -- perhaps a CHECK here or a foreign key to an 
      -- action code table, e.g. 'made active, made inactive'
    who NVARCHAR(64) NOT NULL,
    when DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    why NVARCHAR(MAX)
);

StackExchange Database Administrators Q#4916, answer score: 3