Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

But what do you do in the case of dates, datetime, and time (SQL Server 2008)? You'd have to use some historic or bottomed-out date.

Any ideas on this?

share|improve this question
2  
This answer has an insight on usage of NULL dba.stackexchange.com/questions/5176/… –  Derek Downey Aug 30 '11 at 21:24
1  
Really? Why does RDBMS allow us to use NULL at all, if we shouldn't use them? There's nothing wrong with NULL as long as you know how to deal with them. –  Fr0zenFyr Aug 13 '13 at 6:19
    
Was this a BI data modeling? You generally should not allow nulls in fact tables... otherwise, nulls are you friends when used properly. =) –  sam yi Sep 3 '13 at 18:08

6 Answers 6

up vote 101 down vote accepted

I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"

Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my birth date? What are you going to put in the column until you know? If you're anything like a lot of anti-NULL folks, you're going to enter 1900-01-01. Now I'm going to be placed in the geriatric ward and probably get a call from my local news station congratulating me on my long life, asking me my secrets to living such a long life, etc.

If a row can be entered where it is possible that you don't know the value of a column, I think NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown - a value which others will have to already know, reverse engineer, or ask around to figure out what it means.

There is a balance, though - not every column in your data model should be nullable. There are often optional fields on a form, or pieces of information that otherwise don't get collected at the time the row is created. But that doesn't mean you can defer populating all of the data. :-)

Also the ability to use NULL can be limited by crucial requirements in real life. In the medical field, for example, it can be a life-or-death matter to know why a value is unknown. Is the heart rate NULL because there wasn't a pulse, or because we haven't measured it yet?

Don't be afraid of NULLs, but be willing to learn or dictate when and where they should be used, and when and where they shouldn't.

share|improve this answer
5  
I think I agree with you. What I think is even more important, though, is that the standard is set and followed. If there is no string input, across the board it has to be NULL, instead of an empty string. I can't stand expecting one or the other for the lack of data. +1, @AaronBertrand. –  Thomas Stringer Aug 31 '11 at 1:28
9  
@bignose so you have an entity like Customer, and 4 or 5 attributes that you may or may not know when the row is created, you should have 4 or 5 tables for the extra attributes? And create a new table each time you add a new attribute? I think you can maintain that in a classroom but not in a production system. What is wrong with having NULL instead of an absent row? Logically they are similar but now your joins become quite tedious to write... –  Aaron Bertrand Sep 25 '11 at 20:39
7  
@bignose you're avoiding my question. I think your answer paints NULLs as this wicked witch of the west, and most of your reasoning involves lack of education about NULL, but you ignore what to do when you have an entity with several possibly unknown attributes. So to avoid NULLs entirely, you'd advocate a design that had 15 tables that may or may not have rows, compared to a single table with 15 nullable columns? You'd have a real hard time pulling that off in any of the shops I've worked/consulted. –  Aaron Bertrand Sep 27 '11 at 11:34
6  
I haven't advocated avoiding NULLs entirely with current SQL capabilities. Rather, I point out the failings of NULLs and their undesirability, and advocate changing the RDBMS state of art to a point where we can feasibly avoid NULL. –  bignose Sep 27 '11 at 21:47
7  
@bignose your suggestion above was advocating putting those "truths" in separate tables. All that does is shift the "dealing with NULL" problem from a COALESCE to an OUTER JOIN - and lots more of them if we're talking about more than one attribute. I don't see anything in there about changing the way the RDBMS works. Perhaps you could elaborate and explain how that would work in this context. –  Aaron Bertrand Sep 27 '11 at 22:36

Fabian Pascal has an exchange with someone asking essentially the same question: “NULL confusion”.

Established reasons are:

  • NULL is not a value, and therefore has no data type. Nulls need special handling all over the place when NULL is allowed.

  • NULL breaks two-value (familiar True or False) logic, and requires a three-value logic. This is far more complex to even implement correctly, and is certainly poorly understood by most DBAs and just about all non-DBAs. As a consequence, it positively invites many subtle bugs in the application.

  • The semantic meaning of any specific NULL is left to the application, unlike actual values. Semantics like “not applicable” and “unknown” and “sentinel” are common, and there are others too. They are frequently used simultaneously within the same database, even within the same relation; and are of course implicit and indistinguishable.

  • They aren't necessary to relational databases, as argued in “How To Handle Missing Information Without Nulls”. Further normalisation is an obvious first step to try ridding a table of NULLs.

This doesn't mean NULL should never be allowed. But likewise, it does argue that there are many good reasons to disallow NULL wherever feasible, and try very hard to make it feasible to avoid NULL.

share|improve this answer
2  
Your link to "How To Handle Missing Information Without Nulls" shows quite nicely why we can't do without nulls: Several of the suggestions would be impossible to implement in a rational way on the major RDBMSs as they currently stand. –  Jack Douglas Sep 24 '11 at 12:28
4  
Jack: Right, but “the current implementations can't do it” is not an argument for the status quo :-) –  bignose Sep 24 '11 at 12:32
10  
Is that kind of like saying we shouldn't fly because planes aren't perfect? –  Aaron Bertrand Sep 27 '11 at 11:48
4  
No, it's saying that the vendors should stop invoking excuses for nulls that might have been valid ones forty years ago, but have long outlived their reasonable retention period. I/O times are no longer in the order of magnitude of 80ms. Single CPU cycles are no longer in the order of magnitude of microseconds. Memory limits are no longer in the order of magnitude of a few Megs. Unlike forty years ago, the hardware speeds and capacities needed for working without nulls now DO exist with the cost not being prohibitive. He's saying it's time to move on. –  Erwin Smout Oct 5 '12 at 13:30
1  
Actually I found one area where they are necessary in relational databases, namely in foreign keys, particularly if it is a composite key. You can probably factor these out to some extent but you cannot have a two column foreign key where one portion is NULL without handling that with some pretty interesting acrobatics DDL-wise. –  Chris Travers Nov 7 '13 at 6:47

I disagree, nulls are an essential element of database design. The alternative, as you alluded too, would be a proliferation of known values to represent the missing or unknown. The problem lies with null being so widely misunderstood and as a result being used inappropriately.

IIRC, Codd suggested the current implementation of null (meaning not present/missing) could be improved by having two null markers rather than one, "not present but applicable" and "not present and not applicable". Can't envisage how relational designs would be improved by this personally.

share|improve this answer
1  
I suggest having a user-defined set of different kinds of null, and a user-defined multi-valued logic to go with them :p –  Jack Douglas Sep 24 '11 at 12:32
6  
Those aren't the only options. You exclude the normalisation alternative: Instead of columns which may or may not have a value, use another table which may or may not have a corresponding row for the first table. The meaning of the presence or absence of a row is entailed in the meaning of the tables, and there's no special-casing of NULL or sentinel values etc. –  bignose Sep 24 '11 at 22:35
4  
The presence of NULL does not necessitate special-casing or sentinel values. Those are just symptoms of how some folks decide to deal with NULLs. –  Aaron Bertrand Sep 25 '11 at 20:43
    
It's worth noting that '' is distinct from null on PostgreSQL (though not Oracle) and so gives you a two-fold marker, and you could use 0 for numeric columns. The problem with 0 though is that it doesn't work for foreign keys. –  Chris Travers Nov 7 '13 at 6:43

Wikipedia's article on SQL Null has some interesting remarks about the NULL value, and as a database-agnostic answer, as long as you are aware of the potential affects of having NULL values for your specific RDBMS, they are acceptable in your design. If they were not, you wouldn't be able to specify columns as nullable.

Just be aware of how your RDBMS handles them in SELECT operations such as mathematics, and also in Indexes.

share|improve this answer

Interesting questions.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

It's more complicated than that. Null has a number of distinct meanings and one really important reason not to allow nulls in many columns is that when the column is null this then means one and only one thing (namely that it didn't show up in an outer join). Additionally it allows you to set minimum standards of data entry which is really helpful.

But what do you do in the case of dates, datetime, and time (SQL Server 2008)? You'd have to use some historic or bottomed-out date.

That illustrates a problem with nulls right away, namely that a value stored in a table can mean either "this value does not apply" or "we don't know." With strings, an empty string can serve as "this does not apply" but with dates and times, there is no such convention because there is no valid value which conventionally means this. Typically there you will be stuck using NULLs.

There are ways of getting around this (by adding more relations and joining) but those pose the exact same semantic clarity problems that having NULLs in the database do. For these databases I wouldn't worry about this. There just isn't anything you can do about it really.

EDIT: One area where NULLs are indispensable is in foreign keys. Here they typically have only one meaning, identical to the null in outer join meaning. This is an exception to the problem of course.

share|improve this answer

Refer to Database Design solutions , there should be no null in Your DB , Except One Situation. It Shouldn't Have Null , Because Reporting , And Writing Query in order to Pull Out Reports would be more, and there Would be more load on Engine. You can make another table , that is Called 'Details of Details' , and you can relate it with your table . so you can do this for any optional data on your DB. there is only on null column allowed in Db, and that is when you design subgroups hierarchical, the first member of Continuum can be null.

share|improve this answer
1  
If your goals or requirements or principle is to have no Nulls, why not do the same for hierarchical structures as well? Is there a point on this exception? –  ypercube Feb 23 '13 at 10:05
3  
I have NULLs in my database and I still can build nice and performant reports on top of it... At the same time, I don't see how a NULL would cause 'more load on Engine'. –  dezso Feb 23 '13 at 10:40
1  
unfortunately i didn't understand your question but If I got this I Explain That the only way of using Nulls in True Database Design , Is when you are trying to Implement a structure like university lessons , because you don't usually know how many layers does the subgroup has , the usual way is trying hierarchical Model; which has a foreign key to itself (Related With itself ) so in the first layer , the Parent_Id should be Null. in Other Situations , there is some Tricks to omit nulls . I hope I did Understood Your question Dude . –  Amirreza Keshavarz Feb 23 '13 at 13:30
1  
when you have nulls in your DB, Always in writing query , you should look after nulls, for your reports , your diagrams, and efficiency of your application drop. with no nulls, End user would be more reliable with it. anyway in small DB s this wont cause any serious problems or load, but in warehouses, or bigger DBs like Enterprises would cause your time many longer to Data retrieve. database design is the most important part of making data stores, you should be very Careful , even if it doesn't make Problems now, someday could. ;) –  Amirreza Keshavarz Feb 23 '13 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.