Database Design: replace a boolean column with a timestamp column?

Question

Earlier I have created tables this way:

create table workflow (
    id number primary key,
    name varchar2(100 char) not null,
    is_finished number(1) default 0 not null,
    date_finished date
);

Column is_finished indicates whether the workflow finished or not. Column date_finished is when the workflow was finished.

Then I had the idea "I don't need is_finished as I can just say: where data_finished is not null", and I designed without is_finished column:

create table workflow (
    id number primary key,
    name varchar2(100 char) not null,
    date_finished date
);

(We use Oracle 10)

Is it a good or bad idea? I've heard you can not have an index on a column with NULL values, so where data_finished is not null will be very slow on big tables.

Answer 1

Is it a good or bad idea?

Good idea.

You've eliminated space taken by a redundant column; the DATE column serves double duty--you know the work was finished, and when.

I've heard like you can't have an index on a column with NULL values, so "where data_finished is not null" will be very slow on big tables.

That's incorrect. Oracle indexes ignore NULL values.

You can create a function based index in order to get around the NULL values not being indexed, but most DBAs I've encountered really don't like them so be prepared for a fight.