5 ms·

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

• crazygringo 6y ago
  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 6y ago
    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.
    • lucian1900 6y ago
      Exactly, that’s what I thought was being described.
    • crazygringo 6y ago
      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.