User manual

• To copy data into a second field that uses the same match fields—
for example, to copy a name and telephone number based on a
matching client identification number—define a lookup for the
second field, and use the same relationship as for the first field.
C235 Client ID
42.83.91.04
C100 Client ID
555-3849
Vancouver
Related file
C200 Client ID
Smith Name
555-1234Phone
New York City
Master file
C200 Client ID
Smith Name
Relationship
555-1234 Phone
Lookup source Lookup source
Lookup
Lookup
destination
destination field field for first field for
field for first
for second lookup second lookup
lookup
lookup
Relational databases and lookups: a comparison
Whether you create a relational database or define a lookup depends
on how you plan to use the data from another file. Use the following
information to help you decide.
Use relational databases to:
• See and work with data from a related file (which can be the same
file) in its most up-to-date state. For example, display data in related
fields when you need the current price of an item. As data changes in
the related records, you see those changes in the master file.
• Set up and manage data efficiently and with flexibility. Instead of
creating many database files that together store multiple occurrences
of data, you store single occurrences of values in smaller files. You
can then work with the data in many ways. You make changes to data
in only one place, which eliminates data duplication and promotes
data accuracy.
• Save disk space, because data is stored in only one place.
Working with related files 8-7
Use lookups to:
• Copy data from a related file (which can be the same file) and keep
it as copied, even when the data in the related file changes. For
example, use a lookup to copy the price of an item at the time of
purchase into an Invoice file. Even if the price in the related file
changes, the price in the Invoice file stays the same.
• Maintain files that already contain lookups, when you don’t want
to change the files to a relational database.
Note A complex relational database or a complex set of lookups may
require an administrator who understands the design and how to
maintain it. The administrator might also train others to use the
database.
Working with relational databases
Important Before you begin, be sure you understand the information
in the previous section, “Relational databases and lookups: a
comparison.”
Planning a relational database
It’s a good idea to plan a database on paper first. Use the following
general steps to plan a relational database.
Note The files in this section are for example only.
1. Begin by asking questions, such as these:
• What information will the database hold?
• What database files are needed?
• What fields will each database file contain?
• What common data exists among the database files?
For example, for a travel agency, you might want a group of files that
holds all the information about your clients, the trips your company
offers, and invoicing data.