Re: NULLs
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 03 Jan 2008 19:52:28 -0400
Message-ID: <477d7540$0$19856$9a566e8b_at_news.aliant.net>
>>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.
Date: Thu, 03 Jan 2008 19:52:28 -0400
Message-ID: <477d7540$0$19856$9a566e8b_at_news.aliant.net>
Gene Wirchenko wrote:
> 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.
And given that commercial dbmses generally allow one to cluster data from multiple tables obviates the performance argument.
[snip] Received on Fri Jan 04 2008 - 00:52:28 CET