Re: Practical considerations of dealing with two meanings of NULLs

On Aug 9, 2:38 pm, paul c <toledobythe...@xxxxxxxx> wrote:
Doug_McMa...@xxxxxxxxx wrote:
On Aug 8, 5:27 am, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> 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.

.

Relevant Pages

  • Re: So whats null then if its not nothing?
    ... >>> A SELECT DISTINCT (it is really too kind to call this SQL ... >> According to the 3vl rules, whether two nulls are distinct or equal is ... Now strip all columns not used in the UNIQUE constraint. ... Clearly, you have a duplicate. ...
    (comp.databases.theory)
  • Re: So whats null then if its not nothing?
    ... > eliminates duplicate values. ... whether two nulls are distinct or equal is ... >> The unique constraint behaviour depends on how you define it. ... SQL constraints are considered satisfied if they are true ...
    (comp.databases.theory)
  • Re: UNIQUE index - constraint BUG/FEATURE?
    ... >> For me it makes no sense to have UNIQUE behaviour as defined within the ... Why would I want to have constraint on NULL values? ... There are very many things make little sense in SQL ... The issue of NULLs is an important and yet controversial ...
    (microsoft.public.sqlserver.programming)
  • Re: So whats null then if its not nothing?
    ... >> A SELECT DISTINCT (it is really too kind to call this SQL ... By the exact same argument, duplicate NULLs should *not* be removed, ... > the constraint is *NOT* satisfied. ...
    (comp.databases.theory)
  • Re: Why does 1:3 relationsihp require another table?
    ... Pro SQL Server 2000 Database Design ... > there can be a relation with NULLs in its tuples (hence 1NF models with ... > can only conclude that you do not believe Louis and Lewis are correctly ...
    (microsoft.public.sqlserver.programming)