Options for eliminating NULLable columns from a DB model (in order to avoid SQL's three-valued logic)?

Question

Some while ago, I've been reading through the book SQL and Relational Theory by C. J. Date. The author is well-known for criticising SQL's three-valued logic (3VL).¹⁾

The author makes some strong points about why 3VL should be avoided in SQL, however he doesn't outline how a database model would look like if nullable columns weren't allowed. I've thought on this for a bit and have come up with the following solutions. If I missed other design options, I would like to hear about them!

¹⁾ Date's critique of SQL's 3VL has in turn been criticized too: see this paper by Claude Rubinson (includes the original critique by C. J. Date).

---

Example table:

As an example, take the following table where we have one nullable column (DateOfBirth):

#  +-------------------------------------------+
#  |                   People                  |
#  +------------+--------------+---------------+
#  |  PersonID  |  Name        |  DateOfBirth  |
#  +============+--------------+---------------+
#  |  1         |  Banana Man  |  NULL         |
#  +------------+--------------+---------------+

---

Option 1: Emulating NULL through a flag and a default value:

Instead of making the column nullable, any default value is specified (e.g. 1900-01-01). An additional BOOLEAN column will specify whether the value in DateOfBirth should simply be ignored or whether it actually contains data.

#  +------------------------------------------------------------------+
#  |                              People'                             |
#  +------------+--------------+----------------------+---------------+
#  |  PersonID  |  Name        |  IsDateOfBirthKnown  |  DateOfBirth  |
#  +============+--------------+----------------------+---------------+
#  |  1         |  Banana Man  |  FALSE               |  1900-01-01   |
#  +------------+--------------+----------------------+---------------+

---

Option 2: Turning a nullable column into a separate table:

The nullable column is replaced by a new table (DatesOfBirth). If a record doesn't have data for that column, there won't be a record in the new table:

#  +---------------------------+ 1    0..1 +----------------------------+
#  |         People'           | <-------> |         DatesOfBirth       |
#  +------------+--------------+           +------------+---------------+
#  |  PersonID  |  Name        |           |  PersonID  |  DateOfBirth  |
#  +============+--------------+           +============+---------------+
#  |  1         |  Banana Man  |
#  +------------+--------------+

While this seems like the better solution, this would possibly result in many tables that need to be joined for a single query. Since OUTER JOINs won't be allowed (because they would introduce NULL into the result set), all the necessary data could possibly no longer be fetched with just a single query as before.

---

Question: Are there any other options for eliminating NULL (and if so, what are they)?

Answer 1

I saw Date's colleague Hugh Darwen discuss this issue in an excellent presentation "How To Handle Missing Information Without Using NULL", which is available on the Third Manifesto website.

His solution is a variant on your second approach. It's sixth normal form, with tables to hold both Date of Birth and identifiers where it is unknown:

#  +-----------------------------+ 1    0..1 +----------------------------+
#  |         People'             | <-------> |         DatesOfBirth       |
#  +------------+----------------+           +------------+---------------+
#  |  PersonID  |  Name          |           |  PersonID  |  DateOfBirth  |
#  +============+----------------+           +============+---------------+
#  |  1         |  Banana Man    |           ! 2          | 20-MAY-1991   |
#  |  2         |  Satsuma Girl  |           +------------+---------------+
#  +------------+----------------+
#                                  1    0..1 +------------+
#                                  <-------> | DobUnknown |
#                                            +------------+
#                                            |  PersonID  |
#                                            +============+
#                                            | 1          |
#                                            +------------+

Selecting from People then requires joining all three tables, including boilerplate to indicate the unknown Dates Of Birth.

Of course, this is somewhat theoretical. The state of SQL these days is still not sufficiently advanced to handle all this. Hugh's presentation covers these shortcomings. One thing he mentions is not entirely correct: some flavours of SQL do support multiple assignment - for instance Oracle's INSERT ALL syntax.

Answer 2

I recommend you go for your option 2. I'm fairly certain Chris Date would too because essentially what you are doing is fully normalizing to 6NF, the highest possible normal form which Date was jointly responsible for introducing. I second the recommended Darwen's paper on handling missing information.

Since OUTER JOINs won't be allowed (because they would introduce NULL into the result set), all the necessary data could possibly no longer be fetched with just a single query as before.

…this is not the case, but I agree the issue of outer join is not explicitly mentioned in the Darwen paper; it was the one thing that left me wanting. The explicit answer may be found in another of Date's book…

First, note that Date and Darwen's own truly relational language Tutorial D has but one join type being the natural join. The justification is that only one join type is actually needed.

The Date book I alluded to is the excellent SQL and Relational Theory: How to Write Accurate SQL Code:

4.6: A Remark on Outer Join: "Relationally speaking, [outer join is] a kind of shotgun marriage: It forces tables into a kind of union—yes, I do mean union, not join—even when the tables in question fail to conform to the usual requirements for union... It does this, in effect, by padding one or both of the tables with nulls before doing the union, thereby making them conform to those usual requirements after all. But there's no reason why that padding shouldn't be done with proper values instead of nulls

Using your example and default value '1900-01-01' as 'padding', the alternative to outer join could look like this:

SELECT p.PersonID, p.Name, b.DateOfBirth
  FROM Person AS p
       INNER JOIN BirthDate AS b
          ON p.PersonID = b.PersonID
UNION
SELECT p.PersonID, p.Name, '1900-01-01' AS DateOfBirth
  FROM Person AS p
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM BirthDate AS b
                    WHERE p.PersonID = b.PersonID
                  );

Darwen's paper proses two explicit tables, say BirthDate and BirthDateKnown, but the SQL would not be much different e.g. a semi join to BirthDateKnown in place of the semi difference to BirthDate above.

Note the above uses JOIN and INNER JOIN only because Standard SQL-92 NATURAL JOIN and UNION CORRESPONDING are not widely implemented in real life SQL products (can't find a citation but IIRC Darwen was largely responsible for the latter two making it into the Standard).

Further note the above syntax looks long-winded only because SQL in general is long-winded. In pure relational algebra it is more like (pseudo code):

Person JOIN BirthDate UNION Person NOT MATCHING BirthDate ADD '1900-01-01' AS DateOfBirth;