Null fields in the database or default to empty string

Posted on 2011-09-06
Last Modified: 2012-05-12
I am writing my data layer at the moment and trying to decide whether to leave the database fields as nullable or set them by default as an empty string ''.

As an example I have an FTP object where the username and password may or may not be set. What is the best practice here. The data layer will be accessible by numerous programs.
Question by:rocky050371
LVL 16

Expert Comment

by:Swapnil Piparia
ID: 36489381
Hi rocky050371,

          Its always good to set database field as nullable. As both empty and null has different representation.

LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 500 total points
ID: 36490082
It depends on who you talk too.

Null is a pain to use and can easily lead to errors. As a consultant and trainer who has to often work on code written by other programmers, I have often seen records (lines) disappear from results because of nulls.

This is a standard one. Suppose that the field State accepts nulls (for the purpose of the demonstration):

SELECT * FROM tbCustomers WHERE State="California"
SELECT * FROM tbCustomers WHERE NOT State="CaliFornia"
SELECT * FROM tbCurtomers

You end up with 450 customers in the first query, 200 in the second one, but 700 in the third. 50 customers where lost in the second one. They were not in California, but did not make it, because the programmer forgot to check for nulls.

SELECT * FROM tbCustomers WHERE NOT State="CaliFornia" OR IsNull(State)

That is but one example.

Nulls are a pain to deal with in code. They are hard to retrieve, because the standard types do not accept a Null. You have to use Nullable types (usefull, but a pain to use) or a lot of Ifs to handle Nulls in your application.

How many times have I seen code that reads a Null, translate it to an empty String to display it in a TextBox, and update the database with the content of the TextBox. Ooops. Lost the Null.

When you work with Nulls, you have to think about them all the time.

In most instances, I would rather have a default value that would have no meaning in real life. Say, "(undefined)" as the default value for the State. "(undefined)" becomes my Null. Or "<Type the state>". Displays as is in a field. The only difference is that I have to check to make sure that the user does not leave it as such in fields where the value is required (through code or trigger), while this is automatic with a Null.

Dealing with it is easier than dealing with Null. I won't lose that one in the query, I won't change it when I update the database from the TextBox. If needed, I can filter it with the same type of code I would have used with a Null. But most of the time, I can let it live as it is without intervention, which is impossible with Null.

Nulls have their use. I would not put a dummy image in an Image field that contains nothing. But for most types, a well chosen default value can take the role of a Null.

Good question. Expect to have an interesting discussion with this one.
LVL 83

Expert Comment

ID: 36492041
Whatever you choose to use, it needs to be standard and all apps need to know the fact. If you choose to use Null and some programmer does not handle nulls in his code, they will get errors. If you use empty string and some programmer is using a Null comparison in code, they will have problems. So whatever you choose, make it consist and communicate it.
LVL 83

Expert Comment

ID: 36492048
>make it consist
make it consistent

Author Comment

ID: 36493966
I agree James and your example of the assigning to a text box I think is very valid. It does seem that it is personal choice, I have to say handling null in objects is a pain.

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now