by Frank Sweet

Database design is full of Compromises.
Data integrity and ease of use are funda-
Mmentally at CTOsS-purposes. We cannot
avoid trading one for the other. The prob-
kem isn't a software limitation; its roots le
in the very reasons we collect and store in.
formation. We want it to be right. We want
it to be on time. We can never have both.
Today we'il look at two examples of com-
promise, the difference between mandatory
and optional interrecord relationships—
hard sets vs. soft sets—and the desirability
of summary fields.

Consider the following Bachman
data structure diagram where we may have
any number of invoices on file for a vendor,
" but each invoice must be related to only

one vendor.
| srece |

There are two ways to physically
implement such a relationship between rec-
ords (called a "set" in Codasyl-cogoL) in

st databases. One is where each vendor
6%... is the Starting point of a chain of
disk address Pointers. (The vendor record
POints to the first of its invoices, which
Points to the second, etc.) In the other,
each invoice simply contains its vendor
number as a data field and Pointer chains
are not used.

Both approaches enable a program
to retrieve the vendor record, given an in-
voice. With the first, it would read the in-
Voice record and then use the database
management system's obtain-owner or get-
parent command. Under the second, the
Program would still read the invoice first,
but then it would move vendor number
from the invoice record to the vendor rec.
ord's direct-access key and read it directly.

Both approaches let us extract al
the invoices for a given vendor, although
the pointer chains do it more efficiently,

We continue our exploration of database design in
parts eight and nine of this 14-part series,
Process-Driven Data Design.

DATA INTEGRITY

AND THE

Here, the program would read the vendor
file, then use its chain-following command
(get next within parent or obtain next with-
in set). Without POinter chains, the pro-
gram would simply read every invoice in
the file and selectively process only those
that contain the given vendor number.

Where the efficiency of given-a-ven-
dor-get-the-invoices activities is unimpor-
tant, the major difference between the
approaches is in the strictness of data vali-
dation that the database management sys-
tem can provide. "Hard set" is where we
use the DBMS itself to Buarantee data integ-
rity—to ensure that each invoice is related
to a valid vendor. "Soft set" js where we
build integrity checking into the applica-
tion. Pointer chains enable hard sets.

With a hard set, each invoice must
be connected to a valid vendor. The rule is
Simple and strict, Look at four sample
Situations:

" Initial oversight. Imagine that a Program-
mer fails to follow specifications or the
specs don't say vendor number must be
checked before SlOring a new invoice rec-
ord. The DBMS would refuse the command
and return an error code unless a valid ven-
dor had been accessed first.

" Subseguent maintenance. Say postimple-
mentation maintenance reguires that users
have the ability to change the vendor with
which an invoice is associated. A gain, if the
changed program neglects to check the new
vendor's validity, the DBMs would disallow
the operation.

" Owner deletion. Let's look at it from the
other angle. If a user or program attempts
lo erase a vendor record from the file, the
DBMS won't allow the operation if any in-
Voices are attached to the vendor.

" User override. Perhaps the user needs the
ability to put invoices into the system be-
fore assigning them to a vendor (maybe
identifying the vendor takes several days).
The DeMs will not allow it.

Don't. misunderstand. We aren't
saying that any particular database pack-
age compels this degree of strictness; none

does. But they enable it, and we're describ-
ing what happens when we take advantage
of the capability.

DANGER OF. Witha soft set, the appli-

ORPHANED  <ation Program—not the
INVOICES DBMS—<controls data in-

tegrity. Look again at the
sample situations. In the case of initial
oversight, a new invoice could be stored
without a valid vendor number or with no
vendor number at all. In subseguent main-
tenance, if the update Program moves a
nonvalid vendor number into the invoice
and modifies it, it will remain inaccurate.
Without hard-set integrity, a vendor could
be deleted, leaving dozens of invoices or-
phaned and no longer meaningful. And, if
the user needs to Put invoices into the Sys-
tem before assigning them vendor numbers,
there's nothing to prevent it.

Which is best? There is no answer.
The trade-off, as we warned, is between
data integrity and ease of Use. Look at it
this way: the power of the hard set is that it
enforces strict referential integrity whether
analysts, programmers, or users want it or
not. That is also the hard set's weakness.

Summary fields are another area of
compromise. Consider a hierarchical data-
base. Not a hierarchical DBMS—that's just
another name for iMs/DLI. ] mean really
hierarchical. You know, with records for
parishes, dioceses, archdioceses, and so
forth, right on up to the Pope. Each parish
record contains a field, number-of-faithful,
telling how many members it has. Each
bishop's diocesan record holds the same
data element, enumerating total parishio-
ners in the diocese, and so on. These are
evidently redundant and are called "sum-
mary fields" because each contains the sum
of the same field in subordinate records.
Summary fields are sometimes useful. They
are always risky.

Sunimary fields are often useful be-
Cause they enable one Program to do the
tedious summarization work for many oth-
ers. For example, Say many programs need

NOVEMBER 15, 19B4 484