Solved

Null fields in the database or default to empty string

Posted on 2011-09-06
5
143 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
5 Comments
 
LVL 16

Expert Comment

by:Swapnil Piparia
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>make it consist
make it consistent
0
 

Author Comment

by:rocky050371
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

16 Experts available now in Live!

Get 1:1 Help Now