Posted by Rico on 04/22/06 08:48

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?

Not trying to raise a debate really, just asking for clarification.


"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1145653630.430902.73680@z34g2000cwc.googlegroups.com...
> Lyle Fairfield wrote:
>> We should be extremely careful when we convert nulls to values. Such
>> conversion could lead to error. Often it is persons without strong
>> grounding in mathematics and logic who make these conversions,
>> increasing the likelihood of such error.
>
> You think so? Nulls as formulated in SQL totally defy any standard
> mathematics or logic. Any system that permits the predicate (x=x) to
> evaluate to anything other than true isn't likely to win many votes
> from persons with a strong grounding in mathematics. It is precisely
> because nulls are so counter-intuitive that they lead to so many
> mistakes in SQL. However, I do agree with your basic point that if you
> regularly need to convert nulls like this it may indicate weakness in
> your design or requirements.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

[Back to original message]