.comment-link {margin-left:.6em;}
Books & Articles I wrote.

Thursday, January 26, 2006


Using Database NULL's

Whilst trying to figure out the best way to make use of the Nullable C# value types when saving the data base to the database (i.e. do i use DBNull or something else?) i discovered a few interesting facts and a nice explanation of when to use NULLs. Basically using them should be the exception (although i have seen many an application use LastModifiedDate set to some strange date rather than NULL even when the thing has just been created!).

If you have fixed length fields, they will always be the full width, regardless of whether you allow NULLs or not. I don't think this is an argument against the use of nulls.

The biggest reason for NOT using NULLs is that they are so meaningless. You
have no idea what it means if someone enters a NULL. Does it mean the value
isn't known now, the value can never be known, the value isn't relevant, or
something else.


I think the important thing to remember about NULLs is that they are very different from a zero value or an empty string. Logically a NULL value is supposed to represent something more like "unknown" or "not applicable". For example, say that you are creating a human resource system, and as part of the system you have to track retired personnel as well as currently employees. You very probably will need columns something like "Termination_Date" and "Death_Date". In this instance, a NULL is either column probably means "not applicable" ... in other words, the individual is either still employeed and/or still living. No other value logically represents that, and so a NULL is not only a reasonable choice, but is the logically correct choice for these columns.

The proper handling of NULLs in queries can be difficult for those not well-versed in three-valued logic, so if it is possible to avoid a NULL in a column, then that is almost certainly the better choice. But when NULL is the logically correct entry, then, IMHO, it simply makes the logic even more difficult by trying to create a "dummy" value to represent "unknown" or "not applicable".

In short, the Nullable datatypes on my interfaces are stil useful as i do have NULL ID's for some objects, before they are saved to the database. So i guess the order is:

1. If you can put a value in, or a fk reference, then do so and make the column non-nullable.
e.g. You have an ID=5 or a fkID=6

2. If you have a null C# value, then can you always use some reasonable default value either within C# or the database? If so, do that and make the column non-nullable.
e.g. You have a displayName of NULL, but can set displayName=username or ""

3. If you have a null C# value and can't make the value into any sensible default, then use NULL.
e.g. You have an EndDate of NULL and it doesn't make sense to use a default date value. However, if you had a Boolean "Ended" you may default that to false in thise case, rather than leaving it as NULL.

 read 0 comments | 

Nullable Value Types Irritation

I have to say working with these Nullable Value Types is a little irritating. Mainly because you can't go implcitly from int? to int and so although in the context of your Entity you may allow a null ID, the concept of a null ID may NOT be something you wish to expose to method calls using the object or at least avoid you having to type everything int? and then check hasValue.

Here's is what I am talking about.

You define a simple element entity as follows:

public class Element{
int? _id = null;

public int? ID
get { return _id;}
set {_id=value;}

You then may have a data object as follows:

public class MyData
public void Save(Element element)
if (element.ID == null)
Update(...); }

The issue you now have is that any business methods, helper methods and so on that use this ID can then expect two types of query. The first is where you have a valid ID (number > 0) and the second is where you have a null value. Therefore, everytime you want to call a method such as the following, which will pass the ID of the element to the method you ALWAYS have to do hasValue.

public class MyBusiness
public void AttachToItem(int? id)
if (!id.hasValue)
throw new Exception("...");

//Do Something ....
Yes, before we had to do a check for -1 or some other magic number, but i'd rather there was an up front way of saying - in this certain scope it may be nullable, but outside it must have a value before it can be used as a parameter and the above method defined as follows:

public class MyBusiness
public void AttachToItem(int id) //i now have a valid int !
//Do Something ....
The only way i can think of solving this without writing loads of code, would be to have some kind of c#meta which allows you to put constraints on the variables in terms of scope, value, casting and exception.

So I may define my nullable int? as follows (making all this up now):

int? _id = null : valuecast(condition[value>0], IDException);

This basically says, define my _id variable as a nullable int? and when it is cast to an int value - [ in order to solving having to check for nulls in all my methods and so allow then to be defined as proper int value types ] - if the current value of _id is not greater than 0 then throw the user defined IDException.

Something like this would then provide me with a way of casting all of my fields for my entity throughout my entire application, which allows me to ensure that i do have a valid int (and it is NOT null) which is needed is almost all of my cases and my interfaces outside my entity can just use int's to define their interfaces.

 read 0 comments | 

This page is powered by Blogger. Isn't yours?

Weblog Commenting and Trackback by HaloScan.com