Re: NULLs

Hugo Kornelis <hugo_at_perFact.REMOVETHIS.info.INVALID> wrote:

[snip]

>When implementing relations in what is currently usually called a
>relational database, it *is* possible to create a table per elementary
>fact type. In that case, no NULLs are ever needed. The facts
>
>* "Employee Jack is male"
>* "Employee Mary is female"
>* "Employee Jack is 43 years old"
>* "Employee JJ is 32 years old"
>
>are represented in two tables, like this:
>
>EmployeeAge EmployeeGender
>name | age name | gender
>-----+----- -----+--------
>Jack | 43 Mary | female
>JJ | 32 Jack | male
>
>But for various reasons (maintainability and performance being the most
>important), it is often preferable to combine these relations in a
>single table, like this:
>
>Employee
>name | age | gender
>-----+-----+-------
>Jack | 43 | male
>JJ | 32 |
>Mary | | female
>
>Some way has to be found to ensure that JJ's gender is not mistaken as
>'' (empty string), and to ensure that no datatype mismatch error is
>raised on the blank age of Mary (as a blank is not numeric data). The
>best (or, if you prefer, least bad) answer to that problem is NULL.

     Which make the one-table example less maintainable because of the special cases. As to performance, I quite often query for very limited numbers of columns, so the issue of columns being split up into different tables is not the problem it might appear to be.

>Coming from this background, you'll probably understand why I have
>little issue with NULL - as it is (to me) just a marker that says "hey,
>if you hadn't insisted on stuffing the fact types EmployeeAge and
>EmployeeGender in the same table, there simply would have been no row
>for Mary in the EmployeeAge table".

     Or "Hey, if you had not gotten it wrong, it would not be necessary to kludge." Why kludge? (Measure twice, cut once, and all that.)

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.