Re: Practical considerations of dealing with two meanings of NULLs

From: <>
Date: Thu, 09 Aug 2007 11:00:00 -0700
Message-ID: <>

On Aug 8, 5:27 am, Bob Badour <> wrote:
> Use multiple relations and no NULL.

I've found this unwieldy to implement in practice, as it leads to an explosion of tables in current SQL databases, if you have a lot of "optional" values (in the limit, you could end up with a lot of twocolumn  tables). Even if you undertake to do so meticulously for your base relations, NULLs (or what Codd called marks) will inevitably resurface in views, unless outer joins are disallowed. And what's possible in a view relation should by symmetry also be possible for base relations. Proposed solutions for that (e.g. default values) seem to me like cures that are worse than the disease. Disallowing outer joins would be cleaner, but it complicates many use cases, particularly situations where your data model is trying to support an application model that has object class inheritance of some sort (or the similar idea of discriminated unions). It also complicates applications that have varying constraints as an object progresses through some sort of life-cycle, as this would again lead to an explosion of tables, while requiring the application to somehow switch tables as the object's state changed. Perhaps this says more about the state of higher-level languages and tools for creating data-bound interfaces, nevertheless that is a reality that designers need to include in their thinking.

All that said, I basically agree that NULLs can in principle always be banished, and they create endless headaches for developers trying to write correct queries (although, on the plus side, they do provide a rich source of trick interview questions!). I'd be very interested to hear how you deal with class-inheritance and life-cycle constraint variants when you construct NULL-free designs. In my own work, I treat NULLs like gotos; I avoid them, but I don't get hung up if I have to trot one for a particular situation. Received on Thu Aug 09 2007 - 20:00:00 CEST

Original text of this message