databasedev.co.uk - database solutions and downloads for microsoft access

Defining Null Values:

A Null represents an unknown or a missing value. It is important to understand that a null value does not mean a zero of a text string of one or more blank spaces.

• Zero (0) can have many different meanings. A zero can represent the balance of your bank account, the amount of a product in stock or the amount of art classes with spaces left on them.
• A text string of one or more blank characters will mean absolutely nothing to most people, however it may be particularly useful to a query language such as SQL. In SQL a blank space is a valid character, as is a string of two blank spaces ('  '). They hold the same importance as a string of two letters ('XY'). In figure 1 shown below, a blank represents the fact that Warrington does not appear to be located in any County.
• A zero-length string, two consecutive quotes & no space between (''), is also deemed an acceptable value to query languages such as SQL and can be meaningful in certain circumstances. You may have used databases containing an Employee table, and in this table along with FirstName and LastName fields you will also see a MiddleInitial field; this may often contain a zero-length string due to the fact that an Employee may not have a Middle Initial.

Figure 1: The Clients Table

ClientID	FirstName	MiddleInitial	LastName	City/Town	County
001	James	M	Lister	Warrington
002	Samual		Helms	Walsall	West Midlands
003	Tracy	L	Sanders	Liverpool	Merseyside
004	Henry		Thomas	Sale
005	Tim		Lee	Scarborough	North Yorkshire
006	Elizabeth	H	Brown	Worthing	West Sussex

The Value of Nulls

When used for its stated purpose, null can be quite useful as shown in the Clients table in figure 1. The null values seen in the ClientMiddleInitial field and the ClientCounty field represent either missing or unknown values. In order to use a null correctly, you will need to understand why they occur:

Human error is commonly associated with the resultant missing values. An example of this may be seen if we consider the record for Henry Thomas in the above table. If you are entering the data for this record and you fail to ask the client for the County name they live in the data will then be considered missing and as such the record will be shown including a null. Once this error is realised however, you can then correct it by calling the client.

Unknown values can appear for a variety of reasons. An example of this may be that the value for a field has not yet been defined. On a student course database, there may be a Categories table that defines that type of courses available; you may add a new course to your list, but as yet there may not be a correct category defined that this course will fit in to. Another example of an unknown value is that it is truly unknown. If we refer back to the example concerning Henry Thomas contained in the Clients table; when entering the data for this record, you have asked him for the County that he lives in, he may not actually know the county name. If you also do not know the name of the county in question, then at this moment in time, the value is truly unknown. You can return to correct this problem when either of you determines the correct county name.

You may also have a case for a null value if you are working in a database and none of its values applies to a record. An example of this may be the Employees database and a table that contains an HourlyRate field and a Salary field. You may see null records here as the fact is the employee won't be paid a fixed salary and an hourly rate, therefore one will always be null.

You must also consider that there is a slim difference between "is not applicable" and "does not apply". In the previous example, "does not apply" is the correct definition as the value definitely can not apply to one or the other of the fields. As another example, suppose you are working in a database that includes a Patient table, and that the table includes a field relating to Hair Colour; if you are updating this and a patient recently lost all of their hair then the field will need to include "not applicable". You could use a Null value in this field, although it is recommendable to use a true value such as "N/A" or "Not Applicable", making information much clearer to the user.

Allowing Nulls in a table will depend on exactly how you are wishing to use the data.

Below deals with the negative elements of using Nulls within your database:

The Problem with Nulls

Including null values within your data can have an adverse effect when using this data within any mathematical operations. Any operation that includes a null value will result in a null; this being logical as if a value is unknown then the result of the operation will also be unknown. Below shows how using a null in a calculation will alter the outcome:

(100 x 3) + 10 = 310

(Null x 4) + 10 = Null

(20 x Null) + 100 = Null

(20 x 100) + Null = Null

Using the products table below, you can see the effects that including Nulls will have on any mathematical expressions you have used:

Products

ProductCode	ProductName	CategoryDescription	Price	QtyInStock	StockValue
001	Word 2002 Bible	Books		10
002	Access 2002 Bible	Books	29.99	5	149.95
003	28" TFT JVC Television		995.00
004	Nokia 310 Mobile Phone		65.00	10	650.00
005	14" Sanyo Portable TV	Audio/Visual	149.99
006	128mb Pen Drive		49.00	15	735.00

Notice how including nulls in a table will effect mathematical operations

You can see an example of the problem that null values cause when looking at certain records in this table. In this table, the StockValue field derives its results by using the Price and the QuantityInStock values, i.e. [Price]*[QtyInStock]. You can see that for the first record, ProductCode 001, that where a user has not entered a value for the Price field (i.e. a Null) then the StockValue also shows as Null.

This can lead to more severe problems as this may go undetected. Suppose you need to calculate the value of all items that you currently have In Stock; you will not receive an accurate result as values will not be taken into account.

To ensure that the Stock Value can always be calculated, you must first ensure that the Price and the QtyInStock fields can never contain a NULL value.

A further example of the effects of null values can be seen below:

Null values will also effect aggregate functions that incorporate the values of a given field. If we were using an aggregate function, for example a Count function, the result will always contain a null if the field contains a null. The table below shows the results of performing a summary calculation that will count the number of occurrences of a category in the Products table.

Category Summary

CategoryDescription	Total No. Of Occurrences
	0
Audio/Visual	2
Books	2

Null values will effect the results from an aggregate function

The Total No. Of Occurrences is the result of using the expression Count([CategoryDescription])

The table shows that there are "0" occurrences of the unspecified category, implying that each Product has been assigned to a category. This is clearly not the case, as viewing the Products table will show that there are two products that do not belong in any category at present.

Using all of this information, you should now be able to see that allowing missing or unknown values will have adverse effects on other things that you may wish to do with your data and should be considered when in the process of database design.