A binary flag with three values

While troubleshooting a problem in the Order entry module of Oracle Applications (11.0.3), I query the column open_flag in the order header table so_headers_all for the order number in question. The column open_flag is supposed to be a Y/N flag that tells me whether the order is open or closed. Instead, for this order, the open_flag is NULL.

Come on! how am I supposed to know the meaning of NULL here? Does null mean ‘Y’ (order open) or ‘N’ (order closed). There should have been a NOT NULL and a CHECK constraint on the open_flag column.

In my definition, a flag is supposed to have two, and only two, values, Y/N, 0/1… Why allow a flag to have a third value, NULL? It only adds to the confusion and is definitely a bad design.

Relying on a NULL to give a meaning to a column is a bad practice.

Filed in Oracle with 5 Comments | Tags: flag, null

---