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

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.

There is also a duplicate-checking process which identifies potential duplicates between customers, using several of the optional attributes (e.g. First Name, Middle Name, Last Name, Birth Date, as a unique identifier is not always available). The current queries are complicated enough (and NULLs are easily handled with ISNULLs). Increasing the number of tables just to avoid NULLs would be a nightmare in this scenario (unless outer joins were permitted, but then you'd end up dealing with NULLs anyway).

> But if more were required, so what? You've got to write the code to sort it
> out somewhere, so why not in the query?

I would weigh up the advantages of avoiding NULLs with the extra overhead incurred by creating the extra tables (in terms of complexity in querying the data). In some cases I do create extra tables, but I just find the thought of taking this approach to the extreme to avoid all NULLs to be a bit, well, extreme.

Chris Received on Tue Sep 19 2006 - 09:38:02 CEST