Re: Practical considerations of dealing with two meanings of NULLs
From: paul c <toledobythesea_at_oohay.ac>
Date: Thu, 09 Aug 2007 21:38:20 GMT
Message-ID: <gPLui.44198$fJ5.14920_at_pd7urf1no>
>
> 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 two-
> column 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.
>
Date: Thu, 09 Aug 2007 21:38:20 GMT
Message-ID: <gPLui.44198$fJ5.14920_at_pd7urf1no>
Doug_McMahon_at_yahoo.com wrote:
> On Aug 8, 5:27 am, Bob Badour <bbad..._at_pei.sympatico.ca> 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 two-
> column 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.
>
While looking forward to what Bob B might have to say about this and in
spite of being rather ignorant of today's class-inheritance and
life-cycle methodologies (eg., I don't know what a constraint variant
is), to me it does have the ring of a true tale from the trenches, which
we don't get much of here.
Also, I like the use of the word "optional" applied to null values, it
A couple of points puzzle me:
- why is the "symmetry" of ensuring that base relations permit whatever virtual relations permit so important? If it is, is it also important they deny whatever virtual relations deny? (eg. a projection view is likely in most products to deny inserts, whereas most dbms's would soon stall without inserts to base table.)
- assuming that SQL products advocated NULLs before class-inheritance languages came along, is it the case that NULLs luckily make the use of those easier or rather is the case that it was consciously decided for those tools to try to take advantage of NULLs?
p Received on Thu Aug 09 2007 - 23:38:20 CEST