Re: Practical considerations of dealing with two meanings of NULLs

On Aug 9, 2:38 pm, paul c <toledobythe..._at_oohay.ac> wrote:
> Doug_McMa..._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
> brings back memories of many conversations about nice-to-have values
> that didn't matter much to the apps, because by definition, they were
> optional!
>
> A couple of points puzzle me:
>
> 1) 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.)
>
> 2) 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

As I understood it Codd envisioned views as a means of achieving logical/physical independence. There is therefore considerable value in ensuring that to the extent possible view relations and base relations are not distinguishable by applications. Sadly, SQL databases have traditionally done a poor job of making views "writable", leading to view-based reads and base-table writes as common industry practice. Received on Fri Aug 10 2007 - 16:54:03 CEST