Solved

Null fields in the database or default to empty string

Posted on 2011-09-06
5
146 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
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 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.
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

14 Experts available now in Live!

Get 1:1 Help Now