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.

This question already has an answer here:

I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL and Relational Theory: How to Write Accurate SQL Code (ch.4, p.79), where the author makes the case that simply because having NULLs produces unexpected results in certain situations, they should be avoided.

The alternative to NULL-able fields, as I understand, is to create 1::0-1 relationships for each field. This seems like it would make querying information very difficult. Is this the correct approach? Are NULLs, in fact, much safer than the author leads me to believe?

Would using a VIEW to represent the many 1::0-1 relationships be applicable and any more beneficial than simply having the NULLs in table in the first place?

My scenario is a user table with optional fields that may, or may not, be foreign key relationships to other tables. Obviously, making those fields NOT NULL means they must be required, but I can't put 0 in there since it breaks the foreign key. Other situations occur when trying to put 0000-00-00 for, say, a birthdate field, since MySQL doesn't allow me to do that.

Thus, I'm back to wondering if eliminating NULLs are, indeed, a best practice and provide the greatest flexibility and performance than the alternative.

share|improve this question

marked as duplicate by ypercube, Max Vernon, RolandoMySQLDBA, Paul White, Mark Storey-Smith Apr 5 at 13:51

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5  
Either use Sixth Normal Form, which is a pain in the ass, or live with nulls, like everyone else. –  Neil McGuigan Apr 3 at 19:55
1  
And consider using a DBMS that actually has CHECK constraints and DEFERRABLE constraints, like Postgres instead of MySQL, if you want to experiment with declarative integrity. –  ypercube Apr 3 at 19:59
    
Another option could be to make the column non-nullable, and define a "magic" value that sort of "takes over" the meaning of NULL - like -1 for an INT column. It's sometimes frowned upon - but it makes your queries a lot easier if you can rely on an INT value to always be there for sure. –  marc_s Apr 3 at 20:46

1 Answer 1

I beg to disagree with this author. The results are expected, but may not be the desired values. Null values don't work well with logical comparisons as the value is unknown, as is the result of a comparison.

If you are using nullable columns as query criteria, you do need to handle them appropriately. These are some of method I have used:

WHERE   (nullable = ? OR nullable IS NULL)
WHERE   coalesce(nullable, -1) = ?
WHERE   nvl(nullable, -1) = ?

EDIT: I do agree with the author that avoiding NULL values on searchable fields is desirable, but designing a database without NULL columns can result in other issues. As design goals, avoiding duplicate and NULLs are appropriate, However a database row is a representation of something else, sometimes an imperfect representation.

For code lists values like "Unspecified", "Unknown", "Not applicable" and "To be determined" may be appropriate. If NULL has meaning, then it is may be appropriate to use that meaning rather than NULL for the column value.

You may also want to consider whether the data has value. I do encounter data which has been gatherer because it could be, but is neither used nor useful. Consider the privacy issues or other legal issues resulting from gathering the data.

Nullable columns are common when subtypes occur in the same table. Understanding the subtypes helps in deciding on the appropriate approach to handling these columns. Using separate tables for each subtype can make the design more complex.

share|improve this answer
1  
You know who the author is, don't you? –  ypercube Apr 3 at 22:38

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