82 CHAPTER 4: SCALES AND MEASUREMENTS

You also need to consider laws and accounting rules that
deal with currencies. The European Union has rules for
computing with euros, and the United States has similar rules
for dollars in the Generally Accepted Accounting Practices
(GAAP).
4.

Try to store primary units rather than derived units

. This is not
always possible, because you might not be able to measure
anything but the derived unit. Look at your new tire gauge; it is
set for Pascal (Newtons per square meter) and will not tell you
how many square meters you have on the surface of the tire or
the force exerted by the air, and you simply cannot figure these
things out from the Pascals given. A set of primary units can be
arranged in many different ways to construct any possible
derived unit desired. Never store both the derived and the
primary units in the same table. Not only is this redundant, but
it opens the door to possible errors when a primary-unit
column is changed and the derived units based on it are not
updated. Also, most computers can recalculate the derived
units much faster than they can read a value from a disk drive.
5.

Use the same punctuation whenever a unit is displayed

. For

example, do not mix ISO and ANSI date formats, or express
weight in pounds and kilograms in the same report. Ideally,
everything should be displayed in the same way in the entire
application system.

CHAPTER

5

Data Encoding Schemes

Y

OU



DO



NOT

put data directly into a database. You convert it into an
encoding scheme first, then put the encoding into the rows of the
tables. Words have to be written in an alphabet and belong to a
language; measurements are expressed as numbers. We are so used to
seeing words and numbers that we no longer think of them as
encoding schemes. We also often fail to distinguish among the
possible ways to identify (and therefore to encode) an entity or

property. Do we encode the person receiving medical services or the
policy that is paying for them? That might depend on whether the
database is for the doctor or for the insurance company. Do we encode
the first title of a song or the alternate title, or both? Or should we
include the music itself in a multimedia database? And should it be as
an image of the sheet music or as an audio recording? Nobody teaches
people how to design these encoding schemes, so they are all too often
done on the fly. Where standardized encoding schemes exist, they are
too often ignored in favor of some ad hoc scheme. Beginning
programmers have the attitude that encoding schemes do not really
matter because the computer will take care of it, so they don’t have to
spend time on the design of their encoding schemes. This attitude has
probably gotten worse with SQL than it was before. The new database
designer thinks that an ALTER statement can fix any bad things he or
she did at the start of the project.

84 CHAPTER 5: DATA ENCODING SCHEMES

Yes, the computer can take care of a lot of problems, but the data
entry and validation programs become complex and difficult to
maintain. Database queries that have to follow the same convoluted
encodings will cost both computer time and money, and a human being
still has to use the code at some point. Bad schemes result in errors in
data entry and misreading of outputs and can lead to incorrect data
models.

5.1 Bad Encoding Schemes

To use an actual example, the automobile tag system for a certain
southern state started as a punchcard system written in COBOL. Many

readers are likely too young to remember punchcard (keypunch)
machines. A punchcard is a piece of stiff paper on which a character is
represented as one or more rectangular holes made into one of 80
vertical columns on the card. Contiguous groups of columns make up
fixed-length fields of data. The keypunch machine has a typewriter-like
keyboard; it automatically feeds cards into the punch as fast as a human
being can type. The position, length, and alphabetic or numeric shift for
each field on the card can be set by a control card in the keypunch
machine to save the operator keystrokes. This is a fixed format and a fast
input method, and making changes to a program once it is in place is
difficult.
The auto tag system had a single card column for a single-position
numeric code to indicate the type of tag: private car, chauffeured car,
taxi, truck, public bus, and so forth. As time went on, more tag types
were added for veterans of assorted wars, for university alumni, and for
whatever other lobbyist group happened to have the political power to
pass a bill allowing it a special auto tag.
Soon there were more than 10 types, so a single-digit system could
not represent them. There was room on the punchcard to change the
length of the field to two digits, but COBOL uses fixed-length fields, so
changing the card layout would require changes in the programs and in
the keypunch procedures.
The first new tag code was handled by letting the data-entry clerk
press a punctuation-mark key instead of changing from numeric lock to
manual shift mode. Once that decision was made, it was followed for
each new code thereafter, until the scheme looked like everything on the
upper row of keys on a typewriter.
Unfortunately, different makes and models of keypunch machines
have different punctuation marks in the same keyboard position, so each


5.1 Bad Encoding Schemes 85

deck of cards had to have a special program to convert its punches to the
original model IBM 026 keypunch codes before the master file was
updated. This practice continued even after all of the original machines
had been retired to used-equipment heaven.
The edit programs could not check for a simple numeric range to
validate input but had to use a small lookup routine with more than 20
values in it. That does not sound like much until you realize that the
system had to handle more than 3 million records in the first quarter of
the year. The error rate was high, and each batch needed to know which
machine had punched the cards before it could use a lookup table.
If the encoding scheme had been designed with two digits (00 to 99)
at the beginning, all of the problems would have been avoided. If I were
to put this system into a database today, using video terminals for data
entry, the tag type could be INTEGER and it could hold as many tag
types as I would ever need. This is part of the legacy database problem.
The second example was reported in

Information Systems Week

in
1987. The first sentence told the whole story: “The chaos and rampant
error rates in New York City’s new Welfare Management System appear
to be due to a tremendous increase in the number of codes it requires in
data entry and the subsequent difficulty for users in learning to use it.”
The rest of the article explained how the new system attempted to merge
several old existing systems. In the merger, the error rates increased from
2 percent to more than 20 percent because the encoding schemes used
could not be matched up and consolidated.

How do you know a bad encoding scheme when you see one? One
bad feature is the failure to allow for growth. Talk to anyone who had to
reconfigure a fixed-length record system to allow for the change from
the old ZIP codes to the current ZIP+4 codes in their address data. SQL
does not have this as a physical problem, but it can show up as a logical
problem.
Another bad property is ambiguous encodings in the scheme.
Perhaps the funniest example of this problem was the Italian telephone
system’s attempt at a “time of day” service. It used a special three-digit
number, like the 411 information number in the United States, but the
three digits they picked were also those of a telephone exchange in
Milan, so nobody could call into that exchange without getting the time
signal before they completed their call.
This happens more often than you would think, but the form that it
usually takes is that of a miscellaneous code that is too general. Very
different cases are then encoded as identical, and the user is given
incorrect or misleading information when a query is performed.

86 CHAPTER 5: DATA ENCODING SCHEMES

A bad encoding scheme lacks codes for missing, unknown, not
applicable, or miscellaneous values. The classic story is the man who
bought a prestige auto tag reading “NONE” and got thousands of traffic
tickets as a result. The police had no special provision for a missing tag
on the tickets, so when a car had no tag, they wrote “none” in the field
for the tag number. The database simply matched his name and address
to every unpaid missing-tag ticket on file at the time.
Before you say that the NULL in SQL is a quick solution to this
problem, think about how NULL is ignored in many SQL functions. The
SQL query “SELECT tag_nbr, SUM(fine) FROM tickets GROUP BY

tag_nbr;” will give the total fines on each car, but it also puts all of the
missing tags into one group (i.e., one car), although we want to see each
one as a separate case, because it is unlikely that there is only one
untagged car in all of California.
There are also differences among “missing,” “unknown,” “not
applicable,” “miscellaneous,” and erroneous values that are subtle but
important. For example, the International Classification of Disease uses
999.999 for miscellaneous illness. It means that we have diagnosed the
patient, know that he or she has an illness, and cannot classify it—a
scary condition for the patient—but this is not quite the same thing as a
missing disease code (just admitted, might not even be sick), an
inapplicable disease code (pregnancy complications in a male), an
unknown disease code (sick and awaiting lab results), or an error in the
diagnosis (the patient’s temperature is recorded as 100 degrees Celsius,
not Fahrenheit).

5.2 Encoding Scheme Types

The following is my classification system for encoding schemes and
suggestions for using each of them. You will find some of these same
ideas in library science and other fields, but I have never seen anyone
else attempt a classification system for data processing.

5.2.1 Enumeration Encoding

An enumeration encoding arranges the attribute values in some order
and assigns a number or a letter to each value. Numbers are usually a
better choice than letters, because they can be increased without limit as
more values are added. Enumeration schemes are a good choice for a
short list of values but a bad choice for a long list. It is too difficult to

remember a long list of codes, and soon any natural ordering principle is
violated as new values are tacked onto the end.