User manual

Working with related files 8-7
1 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.
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:
1 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.
1 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.
1 Save disk space, because data is stored in only one place.
Use lookups to:
1 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.
1 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:
1 What information will the database hold?
1 What database files are needed?
1 What fields will each database file contain?
1 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.
C235Client ID
Name
42.83.91.04Phone
ParisCity
C100Client ID
Name
555-3849Phone
VancouverCity
Related file
C200Client ID
SmithName
555-1234Phone
New YorkCity
Master file
C200Client ID
SmithName
Relationship
555-1234Phone
Lookup source
field for first
lookup
Lookup source
field for
second lookup
Lookup
destination
field for first
lookup
Lookup
destination field
for second
lookup