From: Jay on
As far as I'm concerned David, you are a purest and don't really know what
you're talking about. I have read your posts thinking I may learn something
useful - waste of time.

If you're that concerned with NULLs, than use the ISNULL function, that's
what it's there for.

As for me, I'm dropping this thread.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message
news:N_ednToM6J5_HLPWnZ2dnUVZ8rKdnZ2d(a)giganews.com...
> "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
> news:O$enqsNgKHA.1112(a)TK2MSFTNGP04.phx.gbl...
>>
>> Good point: Null is about the absence of a piece of data or the absence
>> of knowledge about it. However, this is also the same kind of thing with
>> an Outer Join: missing or absence of data. So people who don't want to
>> have Null values in their databases - and especially nullable foreign
>> keys - should also forbid themselves to use any kind of Outer Join under
>> any circonstance. You cannot reject something from one hand and take it
>> back with another.
>>
>
> I for one do not "reject" nulls. I merely said that it's important to
> separate the use of nulls from any business concerns. The decision to use
> a null or not rests entirely with the database designer and he or she
> cannot and should not dodge that responsibility by claiming that the
> business needed it all along.
>
> In some SQL shops I have known it is common practice to avoid nulls in
> table and view design. It is much harder to avoid them in SQL queries
> however because SQL creates nulls in all sorts of places - outer joins
> being only one example, SUM() of an empty set being another. The database
> designer cannot always control every query that will be written against
> his database. However, nulls are seldom what is truly required of any
> query that returns data to a user. Developers writing reports or screens
> usually have to spend time hiding or re-presenting nulls (as zeros or
> empty strings for example). If SQL had better support for supplying
> alternative values in an outer join then database developers would surely
> take advantage of that to avoid nulls before the data got into the hands
> of data consumers.
>
> --
> David Portas
>
>


From: David Portas on
"Jay" <spam(a)nospam.org> wrote in message
news:u%23YppffgKHA.2596(a)TK2MSFTNGP04.phx.gbl...
> As far as I'm concerned David, you are a purest and don't really know what
> you're talking about. I have read your posts thinking I may learn
> something useful - waste of time.
>
> If you're that concerned with NULLs, than use the ISNULL function, that's
> what it's there for.
>
> As for me, I'm dropping this thread.
>

I don't know what I said that was "purest". I'm trying to talking about
practical issues and common sense advice rather than make assertions about
what is right or wrong. My advice is to use nulls where they are
advantageous for practical reasons of design and coding. Do you disagree and
if so what alternative advice do you have to offer?

--
David Portas


From: --CELKO-- on
>> As far as I'm concerned David, you are a purest and don't really know what you're talking about. I have read your posts thinking I may learn something useful - waste of time. <<

Dave has a pretty good track record and he is absolutely right.

Newbies who have no idea about RDBMS blindly make all their columns
NULL-able to save typing. They have no idea how to design encoding
schemes, nor how to do basic research for standards.

The most basic principle of designing with NULLs is that they must
have one and only one meaning. I make people put that meaning in a
comment in the DDL and document it. I want them to focus on it. Dave
has a good way to get this focus. I use questions:

1) Is there an industry standard for unknown or missing values (i.e.
ISO sex_code = 0)?

2) Are there more than one kind of unknown or missing values (i.e.
all zeroes versus all nines in ICD)?

3) if I let you use a NULL what does it mean? How do you compute with
it? What are your predicates going to be like? (i.e. using NULL for
end_period_date is a common temporal trick; it must include CHECK
(start_period_date <= end_period_date); it means that the period is
still active)

>> If you're that concerned with NULLs, than use the ISNULL [sic: COALESCE] function, that's what it's there for. <<

LOL! First of all, you don't know about COALESCE! This is like
talking in Hill Billy dialect. And then the content of the sentence
is so damn wrong. NULLs affect aggregate math, predicates, host
language embeddings, code optimization and a ton of other things.

I am writing the fourth edition of SQL FOR SMARTIES and I want to
spend more time on NULLs because of the shock that fries the brain of
Newbies when they try to understand it. Now I have a great quote from
a fried brain :)