Database Design: Do Not Fear NULLs

re: Database Design: Do Not Fear NULLs

Juan José — Mon, 31 Oct 2005 15:19:18 GMT

Great article. I agree :)

re: Database Design: Do Not Fear NULLs

Eric — Tue, 22 Nov 2005 18:39:58 GMT

I also tend to use NULL. However, I have seen others use the NOT NULL DEFAULT "" and insist it is better that NULLS. One of the main reasons they give is that a NULL value cannot be indexed, and therefore slows down searches. Anythoughts on this?

re: Database Design: Do Not Fear NULLs

Ward Pond — Wed, 23 Nov 2005 09:54:05 GMT

Thanks for your question, Eric. My answer got so involved, it turned into a post. Please let me know your thoughts..

-wp

Slight Ado About Nothing: More On NULLs

Ward Pond's SQL Server blog — Sat, 18 Feb 2006 10:36:07 GMT

More on NULLs: a reader asks if NULLs slow down searches.

http://abtech.blackboard.com/webapps/blackboard/content/listcontent.jsp?course_id=_1281_1&content_id=_195108_1

TrackBack — Sat, 03 Mar 2007 08:20:23 GMT

http://abtech.blackboard.com/webapps/blackboard/content/listcontent.jsp?course_id=_1042_1&content_id=_195300_1

TrackBack — Sat, 03 Mar 2007 08:25:28 GMT

re: Database Design: Do Not Fear NULLs

Mark Johansen — Thu, 21 Aug 2008 05:09:44 GMT

I agree completely, but let me add a clarification: Blank should mean "none" or "does not apply" or whatever makes sense in context, while null means "unknown". Confusing these two things is huge problem in a lot of systems. To take an example I used in my book "A Sane Approach to Database Design" available at electrictactics.com (shameless plug for my book), suppose on a loan application we want to check the applicant's credit history and also his or her spouse's credit history. So we have a screen field for "Applicant Social Security Number" and another for "Spouse Social Security Number". If the second field is blank, does that mean that the applicant is not married and so there is no spouse credit history to check? Or does it mean that at the time he visited the bank to apply for the loan, he didn't know his wife's SSN?

Sometimes when I make a point like this, someone will object that we should just create another field to tell us, like in this case, a "marital status" field. But then we're creating two fields with overlapping information. What does it mean if we check the "Unmarried" box and then fill in a spouse SSN? Redundant data like this forces us to do additional error checking.

Similar things apply to numbers. Suppose a data entry screen asks us to enter, say, the number of years of SQL experience that a job applicant has. The person entering the data finds the number on the job seeker's application form illegible. If there is no way to say "unknown", what is the user supposed to enter? Zero? But saying a person has no experience and saying that you don't know how much experience they have are two very very different things.

I visited a real estate site recently that listed the selling price of a certain house as $0. Are they really giving this house away for free? What, is it haunted or something? I can pretty much guess that in this case 0 meant "unknown". But statisticians routinely calculate the average selling price of a house in this or that city. If they're not careful in their programming, a few $0 houses in the database could make it look like property values are plummetting.

Nullity: The Gift (of Nothingness) That Keeps On Giving

Ward Pond's SQL Server blog — Wed, 10 Sep 2008 01:01:51 GMT

Almost three years after the post was originally published in October of 2005, Mark Johansen, author