Re: 3 value logic. Why is SQL so special?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 19 Sep 2006 14:47:05 GMT
Message-ID: <JpTPg.22880$9u.274052@ursa-nb00s0.nbnet.nb.ca>

Chris Lim wrote:

> Roy Hann wrote:
>

>>You are incorrectly assuming that each nullable column gives rise to a
>>separate table.   Based on the three or four dozen databases that I work
>>with regularly that wouldn't usually happen.  In most cases a table will
>>have 5, 10, maybe more nullable columns because it conflates two or three
>>different entity types.  Thus just one or two additional tables are often
>>sufficient.

>
> I do tend to use this approach for sub-typing. e.g. In my Customers
> example, I would split out attributes about a business customer (a
> company) into a separate table, attributes about a personal customer (a
> person) into a separate table, and leave the generic attributes in the
> Customers table.
>
> However in one of the databases I currently work on, there are dozens
> of attributes captured (particularly for personal customers) for
> marketing purposes. These are all optional as a customer does not have
> to supply them. To avoid NULLs, I would end up with dozens of tables,
> complicating the extremely large number of queries that reference them.

Or you could correctly record the known information. In the case of a middle name, you know the customer entered a character string of length zero for the middle name. If the customer was given several options for some question and chose the option to decline answering, you know they chose that option. etc.

I have worked with direct marketing databases with millions of customer records that got that part right. In fact, I worked with one in 1994 right around the time you started your career. Even though the database had no nulls, I was forever explaining to the business analysts why the queries they wrote using outer joins did not behave as expected.

In the ludicrously oversimplified examples you gave with middle names and isnull, had you correctly recorded a known zero-length string for the middle name instead of using NULL, the comparison you gave would become the much simpler "a = b". Received on Tue Sep 19 2006 - 09:47:05 CDT