Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

4 Answers 4

up vote 2 down vote accepted

You have 97% active rows and 95% of queries on this 97%.

A WHERE clause that selects 97% of rows won't be helped by indexing: it isn't selective enough

I'd consider 2 tables for your "Things": ThingActive and ThingCancelled

For the 5% where you query cancelled rows, you read either the Cancelled table only or a UNION/View

A twist on this...

The cancelled table stores only main table ID and the extra columns (so it's NOT EXISTS to find active).

share|improve this answer

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.<something>AuditLog
(
    key INT NOT NULL FOREIGN KEY REFERENCES dbo.<something>(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)
);
share|improve this answer

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

This probably doesn't matter if "less than 3% of the rows are canceled." - let them be scanned anyway?

Oracle:

For the unique constraint, if you include cancelled_seq (from a sequence) in the index then cancelled rows will never cause the constraint to fail

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

pro: you can scan for the cancelled rows quickly if you index that column as rows with all nulls will be omitted from the index

con: obscurity

share|improve this answer

It really, really depends on the data we're talking about.

However, I personally don't like using a NULL to indicate a status (unless it's a foreign key or something of that sort). I prefer an integer with a default of zero, using zero as the way to indicate that the hasn't been cancelled.

My reasoning: If you decide to add another "status" later, you will have to implement complicated logic, instead of just incrementing the integer.


Another option is that you could create a second (almost duplicate) table that has those extra rows. When a is cancelled, move it off into that "cancelled_" table. Of course, that's presuming your original table had very few columns. Otherwise, my normalization instincts start kicking in.

Alternatively, you could create a "cancellation" table that holds the cancelled data with foreign key references back to the main table. But again, it totally depends on your data and your environment.

share|improve this answer

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.