• Dave62 (8/22/2013)


    I've been building databases since I started working with PFS File in the 1980's. :w00t:

    It seems like there is a literal meaning for NULL, which is "unknown", and a practical meaning, which is "data is not present in this field".

    If I declare a CreateDate field as: [CreateDate] [datetime] NULL

    When I select from that table and see NULL in some of the columns for the CreateDate field, I don't start thinking "Gee, I wonder what's in those fields because it's unknown". I know perfectly well that there is no data present because I allowed the record to be saved without requiring data in that field.

    I think anyone is putting a somewhat weird interpretation on "unknown" if they imagine it's possible to ask what value is in a field that is unknown; if a value is unknown, it can't be in the database; if a field doesn't have a value in it. then from the point of view of the database the value for that attribute of the entity represented by the row containing the empty field is not know, so it is unknown. So unknown and not present amount to the same thing. I can see from your comment that you understand that; I've also observed over the years than an awfully large number of people don't.

    Of course you may want to know why it's unknown: that's easy, roughly as Hugo pointed out: put the reason in a separate column. The data is still absent; the value is still not known; but now you know why it is absent and not known.

    Hugo's example with birth date and foundation date is fair enough in one sense, but not in another sense; if we look at "legal person" and "physical person" they have far more attribute differences than just "birth date" versus "foundation date", so rather than having hordes of NULLable columns you may save space and make life easier by having a person table and two extra tables, one for the legal person only attributes and another for the physical person only attributes. Of course even if you do that, you may not have birth date in one of those and foundation date in the other, but a single column (called something like start date or inception date or beginning date) in the person table. That doesn't mean that what he's saying is wrong, just that he's perhaps chosen something which isn't the best example.

    Tom