|
|
Posted by Erland Sommarskog on 04/22/06 13:11
Rico (me@you.com) writes:
> I probably shouldn't open my mouth in the presence of some posters, but
> with regard to converting nulls being bad design; I have a bunch of
> reports that show loans and payments (just to make things simple). If I
> have no payment record (a null) then I have zero payments applied to the
> loan. By converting these null payment records to zero payments, is
> this considered in theory bad design? Or is this an exception to that
> rule. Is there a definition between what would be considered bad design
> and what is considered an exception?
In practice there are many cases where NULL and 0 or the empty string
are more or less the same thing.
Of course, if we have a table:
CREATE TABLE loans (loanno char(11) NOT NULL,
...
no_of_payments int NULL,
....
A NULL in no_of_payments taken to the letter would mean "we don't
know how many payments that has not been done on this loan, if any
at all" or "this is a loan on which you do not make payments at all,
so it is not applicable".
But I don't believe for a second that this is how your table design looks
like. And with a more complex design, there could easily appear a NULL in
a query.
There are many cases were isnull or coalesce comes in handy. For some
computations, equating NULL with 0 makes sense. But coalesce can
also be used to get a value from multiple places. Assume, for instance,
that a customer can have a fixed discount, or he can be part of a
group that can have a common rebate. Assuming that an individual
discount overrides the group discount, that would be:
coalesce(Customers.discount, Groups.discount, 0)
The 0 at the end is really needed here, if we assume that a customer
may not belong to any group. That is, the Groups table comes in with
a left join, so it does not help if Groups.discount is not nullable.
And Customers.discount needs to be NULL, so we can have some logic
to get the group instead. It would not be good to have 0 to mean
"use group instead", because we may actually want to deprive the
customer of the group rebate.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|