?
Solved

Null fields in the database or default to empty string

Posted on 2011-09-06
5
Medium Priority
?
153 Views
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.
0
Comment
Question by:rocky050371
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
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.

Thanks,
netswap
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36492048
>make it consist
make it consistent
0
 

Author Comment

by:rocky050371
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.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

770 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