[lucian1900]

The first approach is one of the steps towards normalising a database.

[crazygringo]

Actually it's the opposite of database normalization.

Normalizing removes data redundancy. This adds data redundancy.

When I design a database structure, it's common to start with the most normalized representation possible. And then to denormalize the minimum necessary for performance reasons -- duplicating rows and/or columns just like here so certain data can be retrieved more quickly, whenever indexes aren't powerful or featured enough.

[fauigerzigerk]

I think what lucian1900 may be thinking is that instead of

    create table purchase_order (
        id int primary key,
        ordered_on timestamptz not null,
        customer_id int not null references customer,
        canceled_on timestamptz
    );

you could have

    create table purchase_order (
        id int primary key,
        ordered_on timestamptz not null,
        customer_id int not null references customer
    );

    create table order_cancelation (
        order_id int primary key references purchase_order,
        canceled_on timestamptz not null
    );

This is indeed a better normalised schema and it allows you to index order_cancelation.canceled_on without worrying about nulls.

[crazygringo]

Oh then, absolutely. I was assuming a constraint that columns couldn't be removed from the original table. But if you can, then your example is an even better solution.

[lucian1900]

Exactly, that’s what I thought was being described.