史蒂文 :: Release 2.0

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.