Should you have NULL’s in your database?

Author: fREW Schmidt

12 Jul

So recently I made a post regarding NULL’s and ” with respect to numeric fields in a database. I asked questions on a couple different mailing lists for help and one of the interesting responses I got was that You Shouldn’t Have NULL’s In Your Database Unless Required.

Now, I totally understand that for strings, which is all the noted article actually discusses. But my issue wasn’t with a string, it was with a number. I’d say that 0 is not the same as NULL when it comes to data. How many kids to you have? Oh you didn’t answer. That must mean none. Seriously?

I think for non-text fields, converting ” to NULL makes perfect sense. After all, when someone does a submit from the browser it must be a string so unless you are doing something special you have to convert and filter and validate the input as a string anyway.

So tell me I am wrong. Show me a good reason why non-string field’s shouldn’t be NULL. I am certainly not as smart as other programmers, but I haven’t seen a good argument yet.

(Note: I meant to post this before but I apparently forgot to press publish :-/)

Filed under: Uncategorized

4 Responses for "Should you have NULL’s in your database?"

Matt S Trout July 12th, 2009 at 11:26 am 1
NULL means “this piece of information exists but is unknown to us”. Follow this simple rule when deciding whether to allow things to be NULL or not and you’re basically sorted – and the standard SQL logic will suddenly work with you rather than against.

Until you do a LEFT JOIN and discover that it uses NULLs for “doesn’t exist” in there … but anyway …
ruz July 13th, 2009 at 4:14 am 2
You’re right, all that “NULLs must die” is about empty strings, while I think that empty string should be treated as NULL. Oracle does this.
Ovid July 13th, 2009 at 7:32 am 3
I’ve a blog entry about this (http://use.perl.org/~Ovid/journal/27927). Basically, NULLs can lead to queries which are logically impossible to get correct answers for. They’re rare, but I’ve hit them on larger queries and they’re a nightmare to debug.

There’s also the problem of what a NULL is supposed to represent. Is the data unknown? Is it not applicable? Is it something else entirely? I often see NULL values in a databsae where people have tried to overload the meaning of NULL and it’s done on an ad hoc basis. For example, consider a “salary” field in a database. Why would it be NULL? Are they unemployed? Are they a volunteer? Do you simply not know it? Are they hourly and therefore not salaried? A NULL value could potenitally have four different meanings.
NULL Conclusion | A Foolish Manifesto July 13th, 2009 at 11:36 pm 4
[...] a couple perl giants I have already heard of responded to my previous post regarding NULL’s in the database. NULL means “this piece of information exists but is [...]