List:General Discussion« Previous MessageNext Message »
From:Ray Date:March 14 2009 8:48am
Subject:Re: avoiding use of Nulls (was: The <=> operator)
View as plain text  
<snip>
>
> I agree with most everything you said, as far as the use of a RDBMS, and
> especially: quoting you,
>
> "theoretically you break up your data into tables so that there is never a
> non-applicable field for any given row."
>
> Here is where I disagree with you, and please DON'T take this as an insult
> (we're just haggling over good/bad practices ), 

No Insult or offense taken. Sorry for opening this can of worms ;)

> but I think it is lazy
> database design when you say:
>
> "In the real world, this can lead to a lot of complexity, so many people
> usually cheat and have a few "does not apply" fields for some records."
>
> OK! Now here is my top 5 explanations why using Nulls is a bad idea:
>
> Explanation(5): The more you understand how the database is to be used,
> and the more complexity and thought you put into your database design, the
> less complex it will be to retrieve reliable information out of it.
> Furthermore, (and this is probably what makes me crazy when Nulls are
> evolved) after a ten year stretch of software development, where I and a
> team designed our own databases, I did a nine year stretch of statistical
> programming, using databases designed by other people, and Nulls in the
> data made the results unpredictable, and yeah, made me crazy! I had to
> write nightly processes to resolve inconsistencies in the data, if at
> least report inconsistencies. You know the old saying "Garbage in =
> Garbage out", to me Nulls are garbage, and if there is a good reason for
> nulls to be a part of good clean data then someone please help me
> understand that.
>
> Explanation(4):I think that the brilliant computer scientists who
> developed the RDBMS database model, didn't want to support nulls
> initially, but they had to because null is a form of data, it means "We
> don't know what this is", and so it must be stored knowing that Null
> should NOT be used as valid information.
>
> Explanation(3):I think it was back in the sixties at Berkley, when some
> smart guy on LSD found a use for the Null value, and now this use of Null
> is taught in Universities all over the world, and considered to be a
> blessed method to practice.
>
> Explanation(2): Using a Null column, and you have an index on that column,
> the index will not be used since NULL values do not get stored in an index
> and that is why the optimizer will need to go to the data pages in order
> to retrieve more information to suffice the requirements of the query.
>
> Explanation(1): I'm just in a argumentative mood today.
>
I'm staying out of the debate on numbers 3,4 and 5, but I am watching with 
interest. Number 2 is very good to know and I will make note of that. as for 
number 1, well ...? 

> Cheers and have a great weekend!
> Mike.

Thread
The <=> operatorMorten13 Mar
  • Re: The <=> operatorMichael13 Mar
    • avoiding use of Nulls (was: The <=> operator)Ray13 Mar
      • Re: avoiding use of Nulls (was: The <=> operator)michael13 Mar
        • Re: avoiding use of Nulls (was: The <=> operator)Thomas Spahni13 Mar
          • Re: avoiding use of Nulls (was: The <=> operator)michael13 Mar
            • Re: avoiding use of NullsAndy Wallace13 Mar
              • Re: avoiding use of NullsArthur Fuller14 Mar
            • Re: avoiding use of NullsPJ13 Mar
            • Re: avoiding use of Nulls (was: The <=> operator)Claudio Nanni14 Mar
              • Re: avoiding use of Nulls (was: The <=> operator)Morten14 Mar
              • Re: avoiding use of Nulls (was: The <=> operator)michael14 Mar
                • Re: avoiding use of Nulls (was: The <=> operator)Mattia Merzi15 Mar
                • Re: avoiding use of Nulls (was: The <=> operator)Claudio Nanni15 Mar
                  • Re: avoiding use of Nulls (was: The <=> operator)Don Read15 Mar
                    • W2008 Server Issues?MCUSA)16 Mar
                      • Re: W2008 Server Issues?David M. Karr16 Mar
            • Re: avoiding use of Nulls (was: The <=> operator)Mattia Merzi14 Mar
        • Re: avoiding use of Nulls (was: The <=> operator)Ray14 Mar
          • Re: avoiding use of NullsAMichel Durand14 Mar
      • Getting single results per (left) record with INNER JOINNigel Peck13 Mar
        • Re: Getting single results per (left) record with INNER JOINNigel Peck13 Mar
          • Re: Getting single results per (left) record with INNER JOINArthur Fuller14 Mar
            • Re: Getting single results per (left) record with INNER JOINNigel Peck14 Mar
              • Re: Getting single results per (left) record with INNER JOINJohan De Meersman15 Mar
                • Re: Getting single results per (left) record with INNER JOINNigel Peck15 Mar
        • Re: Getting single results per (left) record with INNER JOINJoerg Bruehe16 Mar