Alex Rivera | Logout

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

Asked 2010-06-20T16:01:22.213
14

C. J. Date (author of the book SQL and Relational Theory) is well-known for criticising SQL's three-valued logic (3VL).

(Date's critique of SQL's 3VL has been criticized by Claude Rubinson (includes the original critique by C. J. Date) who replied in turn.)

Date 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.

Example table where we have one nullable column:

+-------------------------------------------+
|                   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.

+------------------------------------------------------------------+
|                              People1                             |
+-----
Edit
Report

1 Answer

0

Option 3: Onus on the record writer:

CREATE TABLE Person
(
  PersonId int PRIMARY KEY IDENTITY(1,1),
  Name nvarchar(100) NOT NULL,
  DateOfBirth datetime NOT NULL
)

Why contort a model to allow null representation when your goal is to eliminate them?

answered 2010-06-22T19:57:45.523

Your Answer