You do not need a "salary_value_is_fictitious" field but a field which says why you cannot use the "salary" field.

Nonetheless, it is the same doom I mentioned earlier, even if the flag also indicates why the salary is fictitious. You will still never be able to just say WHERE salary < 100000. With a separate flag field it needs to be WHERE salary < 100000 AND unusable_salary_reason = 0. And heaven help you if someone unaware of this scheme (or just forgetful) runs a query without checking the unusable_salary_reason and takes action based on any fictitious values that appear valid if you look at salary in isolation but are actually flagged as unusable in the other column... (Granted, NULL has odd semantics which can catch the uninitiated by surprise, but they are standardized, so they're much less prone to this kind of problem.)

You are correct that allowing NULL often (not always, but often) calls for a second field to clarify what the NULL means. My basic point of disagreement here is that NULL allows you to indicate within the salary field itself that its value is unusable, while the way you've described of doing it without allowing NULL requires you to check a second field every time you access the salary so that you can determine whether the value claimed by the salary field is usable or not.

allowing "NULL" is by far the worst way to do so, as by its very essence it cannot contain any meaning in and by itself: it is --in a way-- the very absence of meaning.

I like that. "The very absence of meaning" is a good description of NULL and the reason why I support its use: If the value of a field is meaningless for a given record, then NULL allows you to indicate this fact within the field itself. This does not in any way prevent you from also using a second field to indicate the reason why it's NULL.

In reply to Re^8: DBI: passing undef as an argument by dsheroh
in thread DBI: passing undef as an argument by fws

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.