Relational Databases: More Power, More Planning

Robyn Fizz

With the release of FileMaker Pro 3.0, the average computer user can now 
create relational databases. This type of database involves links 
between different files through which data can be shared. In the past, 
creating such databases usually required some degree of programming.

While this article is written in the context of FileMaker Pro 3.0 most 
of the concepts apply to relational databases in general.

The Basics
A relational database lets you display, use, and edit data in one file 
that comes from another file. The data resides only in the related file, 
and the values displayed in the current (master) file get updated 
whenever the data in the related file changes. This promotes data 
accuracy, since you don't need to enter or update data in more than one 
place. In addition, the reduction in duplicate entries saves both time 
and disk space.

Ways of Sharing Information
As an example of a relational database, consider a department 
administrator tracking instructors and classes. The administrator sets 
up a relational database, with a class file and an instructor file. Each 
record in the class file contains information about a specific class: 
its name, unique number, description, prerequisites, schedule, and so 
on. Each record in the instructor file contains data about a given 
instructor: name, office, phone number, and so on. In this scenario, 
each class has only one instructor, but instructors may teach more than 
one class.

Let's look at two ways of sharing data between these files. One way is 
to view instructor information from the class file. That is, when 
viewing a class record, you might want to see who is teaching the class, 
even though the instructor data is stored in a separate file. To do 
this, you create a relationship from the class file (the master file) to 
the instructor file (the related file). You then add a field to the 
class file layout, and choose the instructor data from the Specify Field 
dialog box.

You could also share data between these files by viewing class 
information from the instructor file. That is, you could view all the 
classes taught by a given instructor. Here, you would create a 
relationship from the instructor file (the master file) to the class 
file (the related file). Since instructors may teach more than one 
class, FileMaker requires an additional step. In order to display many 
instances of data (several classes for one instructor), you need to draw 
a layout object called a portal. This is done using the portal tool.

Relationships versus Lookups
If you have used earlier versions of FileMaker, you may be wondering how 
defining relationships differs from creating lookups. Keep in mind that 
a lookup copies data from one file to another. After the data is copied, 
it becomes part of the current file (as well as remaining in the file it 
was copied from). Data copied to the current file doesn't automatically 
change when the values in the other file change.

Defining Relationships
Defining relationships between a master file and related file is done 
through what is known as a match field. In the instructor file, you need 
a field that uniquely identifies each instructor. This could be a field 
that is already part of the database, such as social security number, or 
it could be an "instructor ID" field that contains different values for 
each record (such as INST1, INST2, INST3, etc.). The class file also 
needs an instructor ID field where the appropriate instructor ID is 
stored for each class. These fields become the match fields that define 
the relationships between the files.

Planning a Database
It's probably clear by now that relational capabilities can add 
flexibility and muscle to your databases. To unleash this power, 
however, requires a significant amount of planning. You need to decide 
what information to include in each file, what fields to create, what 
relationships to define, and what common values can work as match 
fields.

Here are some guidelines for thinking through the design of a relational 
database.

1. Be sure you thoroughly understand how relational databases work. 
Review the relevant material in the FileMaker Pro User's Guide and the 
program's online help. You may also want to get some hands-on experience 
in a classroom setting. IS offers an Advanced FileMaker Pro 3 class that 
covers relational capabilities in detail. For more information, call x3-
7685 or send mail to <wray@mit.edu>.

2. Ask yourself what pieces of information (or fields) you want to track 
in your database. A good way to approach this is to write each idea for 
a field on a post-it note and stick the post-its on a wall or 
whiteboard. Break information into the smallest appropriate pieces - for 
example, first name and last name fields, rather than just a name field. 
This gives you more options for sorting and manipulating data.

3. Begin to group the fields into logical categories. Having all the 
fields on post-its lets you rearrange them easily until you end up with 
groupings that make sense. Each grouping should be equivalent to a file.

4. Determine how you will uniquely identify each record in each file 
(e.g., an instructor ID field).

5. Decide how you might want to relate data between files. Which fields 
will store data, and which will use data from other files? Use unique 
identifiers as match fields to establish the relationship.

Having a picture on paper of all the fields, files, and relationships 
makes database creation and layout design significantly easier. This 
kind of planning is a key ingredient to success.

A Parting Caution
Relational databases streamline data entry and give you a lot of 
flexibility in organizing information. The down side of this power is 
that making the wrong moves within the database program can delete data 
permanently in all related files. For this reason you may want to set 
access privileges for your database, and make sure that anyone who 
modifies the database structure understands how the various files and 
fields relate. In addition, it's good practice to edit information in 
its original file.