Hands on: Database values

Ironically, one of the more challenging database problems is not what to do with the data, it’s what we do when we don’t have the data.

Suppose we have a field that is designed to store the number of children for each customer. We set it up as an integer field and start entering data.

When we reach David we realise that we simply don’t know the answer. The immediate temptation is to enter 0 (see attached picture).

The problem is that zero is a number just like 1, 2 or 3. In this case, since we don’t know how many children David has, entering zero is likely to be as inaccurate as entering any of those other values.

Indeed, if we run a query to find the average number of children for our customers we can see that zero is a bad entry. We have six customers for whom we have data. They have 12 children between them, so the average is 2.0 but given this table we get an answer of 12/7 = 1.7.

To cope with this kind of problem, the database world invented the ‘null’ value. If we don’t enter a number here (or delete the zero), then Access enters a null value. A null means, in essence: ‘We don’t know what this value is.’Databases do not treat nulls in the same way as other values. By which I mean, for example, that if we run the query again, Access notices that although we have seven rows of data we only have six values. So it divides the total (12) by the number of non-null values (6) and comes up with the correct answer.

Up, up and away
Upsizing is the gentle art of moving a database from one database engine to another, usually a client-server system. I recently looked at two upsizing tools, SSMA from Microsoft and Must from ASC Associates. Both are useful to have when upsizing from Access to SQL Server, but it is important to realise that no matter how good the upsizing tool, you need to have your brain switched on during the operation. The reason is that databases often have, encoded in their structure, the interaction between the problem we are trying to solve and the features of that particular database engine. An example may help to make this, admittedly somewhat abstract, point clearer.

The problem
You need different people, in different locations, to enter data into an Access database, so you give each person a different copy of the database. You will consolidate the data later into one database. Each new record needs a primary key value that is guaranteed to be unique, but how can you guarantee uniqueness when they are all using different copies of the database?

The solution
Access can generate GUIDs (Global Unique Identifiers). These were mentioned in the October column: usually implemented as Autonumber fields and used as primary keys, GUIDs are unwieldy strings with a very high probability of uniqueness.