• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Null fields in the database or default to empty string

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
rocky050371
Asked:
rocky050371
1 Solution
 
Swapnil PipariaArchitectCommented:
Hi rocky050371,

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

Thanks,
netswap
0
 
Jacques Bourgeois (James Burger)Commented:
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
 
CodeCruiserCommented:
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
 
CodeCruiserCommented:
>make it consist
make it consistent
0
 
rocky050371Author Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now